Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
 Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
Приветствуй всех! Помогите с запросом.
Есть таблица с кодом работников и датами приема и увольнения. Надо посчитать по каждому работнику, в каждой записи,
количество дней между датой увольнения (указана в предыдущей записи) и датой приема (указана в текущей записи).

К сообщению приложен файл. Размер - 30Kb
1 июн 19, 19:52    [21899579]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263


К сообщению приложен файл (tmp.rar - 13Kb) cкачать
2 июн 19, 01:19    [21899672]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
На будущее-прилагайте,пожалуйста,БД(таблицу с данными)-самое тяжелое их набирать
2 июн 19, 01:42    [21899675]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
ели поле по которому идет сравнение не содержит NULL выражение предУв упрощается:
 (select top 1 уволен from таблица1 as t1 where таблица1.принят>t1.уволен  and t1.кодР= таблица1.кодР order by t1.уволен desc)
2 июн 19, 11:15    [21899733]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku
ели поле по которому идет сравнение не содержит NULL выражение предУв упрощается:
 (select top 1 уволен from таблица1 as t1 where таблица1.принят>t1.уволен  and t1.кодР= таблица1.кодР order by t1.уволен desc)

Огромное спасибо! Так тоже работает!
2 июн 19, 12:44    [21899753]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku, а можно при вычислении предУв брать данные/переменные не из таблицы, а из текущего запроса?
бд прилагаю.

К сообщению приложен файл (Работник.rar - 17Kb) cкачать
2 июн 19, 12:51    [21899754]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
wladimirrr
sdku, а можноо при вычислении предУв брать данные/переменные не из таблицы, а изтекущего запроса бд прилагаю.
Данные ВСЕГДА извлекаются из ТАБЛИЦ (хранилищ данных) ЗАПРОСАМИ (какие данные Вы собираетесь "брать из запроса" если поле ЗАПРОСА=полю ТАБЛИЦЫ (за исключением выражений, применения статистических функций или подчиненных запросов).
А Вы разберитесь как работает подчиненный запрос,который отбирает 1 запись из копии таблицы со значением поля [уволен] меньшим чем значение поля [принят] в оригинале таблицы и записывает в поле [предУв] запроса.
(SELECT TOP 1 уволен(выборка из первой записи поля [уволен]) FROM таблица1 as t1 (из копии таблицы,алиас "t1") WHERE таблица1.принят>t1.уволен (для которой дата приема в таблице больше даты увольнения в копии таблицы) and t1.кодР= таблица1.кодР (и [кодР]в копии=[кодР]оригинала) ORDER BY t1.уволен DESC (сортировка по убыванию делает запись с наиболее мало отличающуюся,в меньшую сторону,от [принят] первой в наборе записей- из нее и отбирается [уволен]-значение записывается в [предУв])
Уф! Ну вот-все что знал сказал. (Особенности национальной охоты.)
2 июн 19, 14:09    [21899791]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku
wladimirrr
sdku, а можноо при вычислении предУв брать данные/переменные не из таблицы, а изтекущего запроса бд прилагаю.
Данные ВСЕГДА извлекаются из ТАБЛИЦ (хранилищ данных) ЗАПРОСАМИ (какие данные Вы собираетесь "брать из запроса" если поле ЗАПРОСА=полю ТАБЛИЦЫ (за исключением выражений, применения статистических функций или подчиненных запросов).
А Вы разберитесь как работает подчиненный запрос,который отбирает 1 запись из копии таблицы со значением поля [уволен] меньшим чем значение поля [принят] в оригинале таблицы и записывает в поле [предУв] запроса.
(SELECT TOP 1 уволен(выборка из первой записи поля [уволен]) FROM таблица1 as t1 (из копии таблицы,алиас "t1") WHERE таблица1.принят>t1.уволен (для которой дата приема в таблице больше даты увольнения в копии таблицы) and t1.кодР= таблица1.кодР (и [кодР]в копии=[кодР]оригинала) ORDER BY t1.уволен DESC (сортировка по убыванию делает запись с наиболее мало отличающуюся,в меньшую сторону,от [принят] первой в наборе записей- из нее и отбирается [уволен]-значение записывается в [предУв])
Уф! Ну вот-все что знал сказал. (Особенности национальной охоты.)

Дело в том, что Дата_приема и Дата_увольнения являются результатом выражений. Например, в исходной таблице есть ДатаКонтрактаНачало и ДатаТрДоговораНачало, соответственно ДатаКонтрактаКонец и ДатаТрДоговораКонец. Они заполняются либо Контракт, либо ТрДоговор, т.е одна из двух. Соответственно Дата_приема и Дата_увольнения определяется в запросе, как не пустая. (is not Null). И дальше для вычисления предУв надо оперировать ими.
Поэтому я и спросил, можно в качестве t1 использовать запрос или надо все эти выражения указывать в Вашем коде. Тогда получается более сложный код, в котором я могу заблудиться.))
2 июн 19, 14:40    [21899803]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
используйте в качестве источника не таблицу,а сохраненный запрос (замените "таблица1" на "запросИмя"-источником данных запроса служит таблица или запрос-единственное-он должен соответствовать тому скрину что Вы приложили
2 июн 19, 17:08    [21899852]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku
используйте в качестве источника не таблицу,а сохраненный запрос (замените "таблица1" на "запросИмя"-источником данных запроса служит таблица или запрос-единственное-он должен соответствовать тому скрину что Вы приложили

Отлично, я так и предполагал, спасибо!
2 июн 19, 22:08    [21899954]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
В ФАК написано: "ранее сохраненные запросы тоже можно использовать как источники данных." 6400575
3 июн 19, 01:11    [21900050]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku
В ФАК написано: "ранее сохраненные запросы тоже можно использовать как источники данных." 6400575

Что то я завис(. С таблицей работает, а с запросом не хочет. Нужна помощь! Прилагаю бд, в ней есть таблица QW12 и запрос Qw1_2, они полностью идентичные. Запрос2, который использует табл.QW12 работает, а идентичный Запрос1, который использует запрос Qw1_2 не хочет работать. В чем может быть причина?

К сообщению приложен файл (Работник2.rar - 33Kb) cкачать
4 июн 19, 16:02    [21901659]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
wladimirrr,
Выложите 2 таблицы QW12 и WorkHistory с названиями полей по русски(не страшно если они будут длинными)т.к я не могу понять смысла некоторых. И полно и подробно опишите задачу, стоящую перед Вами-какие исходные данные имеются(будут вноситься оператором) и что Вы хотите видеть на выходе-когда все "вкурю" помогу
4 июн 19, 16:52    [21901710]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku, таблица исходная одна - WorkHistory.
Из этой таблицы в запросе Qw1 я формирую значения От и До для каждого работника. "От" это дата начала работы, "До" это дата окончания работы. Надо посчитать по каждому работнику количество дней между предыдущим окончанием работы (До) и текущим началом работы (От).
Запрос Qw1_2 это производный, более короткий запрос от Qw1.

К сообщению приложен файл (Работник3.rar - 33Kb) cкачать
4 июн 19, 19:31    [21901878]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
alecko
Member

Откуда: Башкирия
Сообщений: 553
не совсем понял о чем речь - надо посчитать дни для работника со времени последнего приема на работу?
аксиомы:
увольнение всегда позже приема. у работника дата приема есть всегда. у неуволенных дата считается до сегодня.

К сообщению приложен файл (Работник2.zip - 25Kb) cкачать
4 июн 19, 20:43    [21901918]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
wladimirrr,
все правильно еще в моем первом примере.Запрос мог не правильно работать из-за неверных данных:посмотрите записи для работника с кодом 14 увольнение 06.09.2019 прием 26.03.2019
дата приема не может быть меньше предыдущего увольнения (аналогично для работника с кодом 16-в прилагаемом файле все исправлено) Такие неверные данные не должны попадать в таблицу-это надо проверять на стадии ввода
А запрос на неверных данных не будет верно работать,если одна запись,то предыдущего увольнения нет в принципе аналогично если поля принят и уволен пусты.
Если Вам надо хранить информацию о контрактах,договорах-делайте отдельные таблицы-не пытайтесь всю инфу всунуть в одну таблицу-это грубая ошибка.
Предлагаю такую схему: Работники связь 1:М с таблицами контракт и договор

К сообщению приложен файл (Работник3.rar - 19Kb) cкачать
4 июн 19, 21:36    [21901957]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku, не совсем то, что нужно.
Давайте еще раз попробуем. Посмотрите в бд Запрос1. Он не работает. Запрос2 работает. Мне нужно, что бы работал Запрос1. Даты я посмотрел, они все корректные.
Разница между Запросом1 и Запросом2 в том, что 1-й использует в качестве данных запрос, а 2-й таблицу, в остальном они абсолютно одинаковые.
Мне нужно, что бы источником данных служил запрос, а не таблица.

К сообщению приложен файл (Работник4.rar - 41Kb) cкачать
4 июн 19, 23:10    [21902002]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
Swa111
Member

Откуда:
Сообщений: 104
wladimirrr,

По производительности может быть хуже, но работает с подзапросами:

select 
 IDhistory,
 IDworker,
 dFrom,
 dTo,
 dtoPrev,
 int(dFrom - dtoPrev - 1) as nDayFromLast

from
 (
	SELECT t1.IDhistory,
	       t1.IDworker,
	       t1.dFROM,
	       t1.dTo,
	       max(t2.dto) as dToPrev
	
	FROM Qw1_2 as t1 left join Qw1_2 as t2 on t1.IDworker= t2.IDworker and t2.dFrom < t1.dFrom
	group by t1.IDhistory, t1.IDworker, t1.dFROM, t1.dTo
 )


Сравнивать проще по дате приема, так как она всегда известна. Другой подход в сравнении периодов с незакрытой датой это приравнивать ее к большому числу. Например у себя в проекте использую функцию на VBA:
Public Function MaxDate(Optional vvDefault = Null) As Date
 If IsNull(vvDefault) Then
  MaxDate = #1/1/2100#
 Else
  MaxDate = vvDefault
 End If
End Function


тогда сравнение выглядело бы
t2.dTo < maxDate(t1.dTo)


и еще замечание! не используйте зарезервированные слова ядра СУБД Access в качестве имен столбцов.
4 июн 19, 23:53    [21902016]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
wladimirrr,
1.Измените названия полей с [from] и [to] на любые другие типа start,finish;ot,do (и впредь НИКОГДА не используйте в качестве имен полей зарезервированные слова,коими являются FROM и TO)
2.Все заработает. Но я не могу понять ЗАЧЕМ 4 запроса (какой глубокий, неведомый смысл в этом),если нужные данные извлекаются,вычисления производятся,осуществляется нужный отбор одним (равносильно поездке из Москвы в Тверь через Владивосток)
5 июн 19, 01:13    [21902041]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
Вдогонку:и еще раз подумайте над структурой таблицы WorkHistory (БД) в которую Вы засунули минимум две сущности:контракт и работу(когда принят и уволен),а может и больше-не вникал.Одно из основополагающих правил "базостроения" гласит что таблица должна полно (в нужном Вам объеме) описывать одну и только одну сущность
Настоятельно рекомендую почитать и усвоить про нормальные формы (в сети куча материала про это)
5 июн 19, 01:38    [21902043]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
wladimirrr
Member

Откуда: Калужская обл.
Сообщений: 316
sdku
wladimirrr,
1.Измените названия полей с [from] и [to] на любые другие типа start,finish;ot,do (и впредь НИКОГДА не используйте в качестве имен полей зарезервированные слова,коими являются FROM и TO)
2.Все заработает. Но я не могу понять ЗАЧЕМ 4 запроса (какой глубокий, неведомый смысл в этом),если нужные данные извлекаются,вычисления производятся,осуществляется нужный отбор одним (равносильно поездке из Москвы в Тверь через Владивосток)

Изменил названия полей, убрал промежуточный запрос, вроде заработало! Спасибо!
Насчет структуры таблицы, я полностью с Вами согласен, просто эту базу разрабатывал не я, мне дали её доработать. Переделывать в ней структуру, себе дороже.
Прикрепляю работающий вариант, может кому пригодится. Еще раз большое спасибо!

К сообщению приложен файл (Работник5.rar - 52Kb) cкачать
5 июн 19, 11:23    [21902299]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
wladimirrr,
пункт 2 в моем сообщении не требует изменения структуры БД (этот пункт верен и при такой схеме)
5 июн 19, 12:40    [21902414]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать в запросе сумму или разницу разных полей в смежных записях.  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
И все-таки Q1 лишний.
(замените в полях запроса1 ссылки на поля Q1 на соответствующие выражения из полей Q1,добавьте поля по которым проводится отбор(не выводить на экран) и сделайте его(запроса1) источником таблицу.Выражения будут довольно большим,но это не страшно-если хотите попроще и понятней осваивайте функции VBA и используйте их в запросе
5 июн 19, 13:24    [21902473]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить