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

Откуда:
Сообщений: 49
Добрый день!
Помогите разработать алгоритм в Access 2012:
Есть таблица итоговая таблица с разбивкой по году и месяцу:
id monthP yearP sumR
1 12 2011 9000.00
1 1 2012 60006.00
1 2 2012 22323.00
1 3 2012 80010.00
1 4 2012 10560.00

мне необходимо получить: Сумму по столбцу sumR (текущий месяц) + sumR (предыдущий месяц), если текущий месяц январь, то надо прибавить декабрь предыдущего года.

На выходе хотелось бы увидеть:
id monthP yearP sumR sumRP
1 12 2011 9000.00 0
1 1 2012 60006.00 69006.00
1 2 2012 22323.00 82329.00
1 3 2012 80010.00 102333.00
1 4 2012 10560.00 90570.00


sumRP - сумма сумм за период.

!При этом один нюанс - бывает что данных за прошлый период нет. И если данных нет- то вычислять не нужно. Оставлять пустое поле:

id monthP yearP sumR sumRP
1 12 2011 9000.00 0
1 1 2012 60006.00 69006.00

1 3 2012 80010.00 0
1 4 2012 10560.00 90570.00

Заранее благодарю за ответы и советы!
23 окт 12, 09:48    [13360332]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
alвк
Member [заблокирован]

Откуда: No comments
Сообщений: 83
Andrey3000,

Было бы хорошо, если бы вы приложили пример БД.
Пока могу сказать только, что сумма за предыдущий месяц будет:
:dsum("sumR";"таблица";"yearP&monthP = " & yearP&monthP & " - 1")
23 окт 12, 10:03    [13360409]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
Dsum("SumR,"таблица","month(дата)=" & month(date))-Dsum(SumR,"таблица","month(дата)=" & month(date)-1)
надо предусмотреть вариант когда текущий месяц меньше предыдущего (январь-декабрь)
23 окт 12, 10:21    [13360514]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
alвк
Member [заблокирован]

Откуда: No comments
Сообщений: 83
sdku,

даже не знаю что тогда в нашем случае сделать, у ТС нет даты. Тогда так(поле в запросе):
:dsum("sumR";"таблица";"dateserial(yearP,monthP,1) = " & dateserial(yearP,monthP,1) - 1 &"")


Надо проверять.
23 окт 12, 10:36    [13360615]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
Интересно-а зачем 2 поля "месяц" и "год" если суть это одно поле "дата" в котором есть еще и день (который когда нибудь обязательно понадобится-может стоит подумать над структурой)
23 окт 12, 10:52    [13360740]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Andrey3000
Member

Откуда:
Сообщений: 49
Спасибо за ответы!
Прилагаю для примера образец

К сообщению приложен файл (Database1.rar - 14Kb) cкачать
23 окт 12, 11:44    [13361184]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Andrey3000
Member

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

Мопед не совсем мой, а данных набрано много
23 окт 12, 11:45    [13361194]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Andrey3000
Member

Откуда:
Сообщений: 49
alвк,

Я конечно забыл указать, что id разные (необходимо для каждого свое) - в примере указал
23 окт 12, 11:55    [13361275]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
alвк
Member [заблокирован]

Откуда: No comments
Сообщений: 83
Andrey3000,

SELECT Таблица1.Код, Таблица1.Клиент, Таблица1.Год, Таблица1.Месяц, 
Sum(Таблица1.Значения) AS итого, DSum("значения","таблица1","month(dateserial(год,месяц,1)) = " & Month(DateSerial([год],[месяц],1)-1) & "") AS пред
FROM Таблица1
GROUP BY Таблица1.Код, Таблица1.Клиент, Таблица1.Год, Таблица1.Месяц, 
DSum("значения","таблица1","month(dateserial(год,месяц,1)) = " & Month(DateSerial([год],[месяц],1)-1) & "");


К сообщению приложен файл. Размер - 27Kb
23 окт 12, 11:56    [13361281]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Andrey3000
Member

Откуда:
Сообщений: 49
alвк,

Спасибо! но такой вопрос - у клиента 1 в 5 месяце считает в "пред", а не нужно

начинаю понимать понемногу, но конечно туговато ...
23 окт 12, 12:14    [13361423]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
alвк
Member [заблокирован]

Откуда: No comments
Сообщений: 83
Andrey3000
Спасибо! но такой вопрос - у клиента 1 в 5 месяце считает в "пред", а не нужно


тогда воспользуйтесь первым моим вариантом в этой теме.
23 окт 12, 12:47    [13361792]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Andrey3000
Member

Откуда:
Сообщений: 49
alвк,
Неудобно даже просить, но не могли бы вы прислать код запроса, я сам наверное еще битый час буду "пробовать"?
23 окт 12, 12:58    [13361926]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
alвк
Member [заблокирован]

Откуда: No comments
Сообщений: 83
Andrey3000,

вот, с учётом клиентов:
SELECT Таблица1.Код, Таблица1.Клиент, Таблица1.Год, Таблица1.Месяц, 
Sum(Таблица1.Значения) AS итого, 
DSum("значения","таблица1","год&месяц = " & [год] & [месяц] & " - 1 and клиент = '" & [клиент] & "'") AS пред
FROM Таблица1
GROUP BY Таблица1.Код, Таблица1.Клиент, Таблица1.Год, Таблица1.Месяц, 
DSum("значения","таблица1","год&месяц = " & [год] & [месяц] & " - 1 and клиент = '" & [клиент] & "'");

Если же вдруг надо пропускать пустые месяцы и брать реально предыдущий, то
SELECT Таблица1.Код, Таблица1.Клиент, Таблица1.Год, Таблица1.Месяц, 
Sum(Таблица1.Значения) AS итого, Dlookup("значения","таблица1","год = " & год & " and месяц < " &  [месяц] & "  and клиент = '" & [клиент] & "'") AS пред
FROM Таблица1
GROUP BY Таблица1.Код, Таблица1.Клиент, Таблица1.Год, Таблица1.Месяц, 
Dlookup("значения","таблица1","год = " & год & " and месяц < " &  [месяц] & "  and клиент = '" & [клиент] & "'")
order by год&месяц
23 окт 12, 13:13    [13362068]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Andrey3000
Member

Откуда:
Сообщений: 49
alвк,

Спасибо большое за помощь! Разобрался!
23 окт 12, 17:41    [13364488]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
alвк,

Первый запрос отлично работает, но, если оба поля с Мес и Годом - числовые, а как будет если вместо двух числовых полей одно поле Дата (dd.mm.yyyy)?
5 июн 19, 09:43    [21902161]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 331
kniga
а как будет если вместо двух числовых полей одно поле Дата (dd.mm.yyyy)?

Покажите пример данных.
Поле "Дата" типа "Дата/Время" или "Текст"?
Сколько записей для каждого клиент/месяц/год? Если одна, то какое число дня используется?
5 июн 19, 12:09    [21902375]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
Кривцов Анатолий,

Поле "Дата" имеет тип "Дата/Время". Для каждого "Сотрудника" (Клиента) используется в месяц одно число - первое (01.01.2019, след мес. 01.02.2019 и т.д.)

На форуме позаимствовал БД (i-bison) и на ее основе делаю свою только для начисления зарплат. Каждый месяц после выплаты остается хвостик и его надо переносить в следующий месяц, чтобы использовать в формуле .. Подскажите, пожалуйста.

К сообщению приложен файл (i_bison_4.rar - 41Kb) cкачать
5 июн 19, 14:13    [21902529]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 331
kniga, если правильно понял, то в свободном поле "Переход остатка" укажите формулу:
=DSum('NZ([зп])-NZ([выдано])';'[трансп_расх]';'[код_МН]=' & Nz([код_МН];0) & ' And [дата]<=' & Format(DateAdd('m';-1;[дата_к]);'\#mm/dd/yyyy\#')) 
6 июн 19, 09:51    [21903188]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
Кривцов Анатолий, да, все именно как и задумывалось ))! Огромное спасибо!!!
6 июн 19, 14:44    [21903571]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 331
kniga
да, все именно как и задумывалось ))!
Ну вот и ладненько.
Функцию DateAdd я применил, когда тащил остаток только из предыдущей записи. Можно без нее:
=DSum('NZ([зп])-NZ([выдано])';'[трансп_расх]';'[код_МН]=' & Nz([код_МН];0) & ' And [дата]<' & Format([дата_к];'\#mm/dd/yyyy\#')) 
6 июн 19, 15:09    [21903624]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
Кривцов Анатолий,

Спасибо за обновленную формулу! Она даже наверно правильней тем, что бывают месяцы. когда не выдается 'зп'!
Правда, вот, что интересно, в bison_4 она здорово работает, а в моей базе никак... Второй день "ломаю голову" не пойму, что я пропустил. Если не трудно, может подскажете?

К сообщению приложен файл (hr_test.rar - 27Kb) cкачать
7 июн 19, 11:43    [21904356]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
тут какая-то 'засада' с версиями ) у меня версия access 2016 и так как я не смог сжать до требуемых мин 150кб, то перенес обьекты в версию 2003, там вроде формула заработала. правда с переходом по записям возникла проблемка.... Вот здесь версия access 2016: https://www.dropbox.com/s/2hs0uefeq3xnchv/hr_test_2016.rar?dl=0, посмотрите, пожалуйста.
7 июн 19, 12:13    [21904392]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 331
kniga
Правда, вот, что интересно, в bison_4 она здорово работает, а в моей базе никак... Второй день "ломаю голову" не пойму, что я пропустил.
В функции вы используете поле "Начислено". Это вычисляемое поле в форме. Его нет в таблице, к которой обращается функция, потому и не работает. Поэтому:
1. Запрос-источник формы сохраните как отдельный запрос. Добавьте в него вычисляемое поле "Начислено" с той же формулой, что и в форме, но с использованием NZ ко всем полям, которые могут быть с Null.
2. Этот запрос используйте как источник формы, а поле формы "Начислено" привяжите к новому полю запроса.
3. В функции обращайтесь не к таблице, а к этому запросу.

По хорошему в запросе должны быть итоговые поля "Начислено всего", "Удержано всего", "К выплате" (разница между ними), "Выплачено" (НаКарту+НаРуки) и "Остаток" (разница между "К выплате" и "Выплачено"). В этом случае функция будет суммировать только поле "Остаток".
К тому же все эти поля пригодятся при печати ведомости и в других местах, где может использоваться этот запрос. А при добавлении/удалении полей или изменении логики вычислений корректировка понадобится только в одном месте - в запросе.
Удачи!
7 июн 19, 12:19    [21904401]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
Кривцов Анатолий,

Спасибо! )
7 июн 19, 17:10    [21904752]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
Заработало!!!

В формуле вместо таблицы написал имя запроса с вычисляемыми полями, как и сказали, правда в форме (она подчиненная) вычисления оставил, потому что выходят ошибки в вычисляемых полях если открывать основная+подчиненная формы (покой нам только снится, работаю над ошибками))

Спасибо, Анатолий!
8 июн 19, 14:45    [21905139]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
а Вы еще это посмотрите-может что "навеет" 21899791
8 июн 19, 15:24    [21905159]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6263
Function Spred(myMes, myGod)
Dim Fpred, Lpred, Ftek, Ltek
Ftek = DateSerial(myGod, myMes, 1) 'первый день месяца
Ltek = DateAdd("m", 1, Ftek) - 1   'последний день месяца
Fpred = DateAdd("m", -1, Ftek)     'первый день предыдущего месяца
Lpred = DateAdd("d", -1, Ftek)     'последний день предыдущего месяца
Spred = Ftek & ", " & Ltek & ", " & Fpred & ", " & Lpred
End Function
?spred(1,1999)
01.01.1999, 31.01.1999, 01.12.1998, 31.12.1998
?spred(2,2000)
01.02.2000, 29.02.2000, 01.01.2000, 31.01.2000
?spred(2,1999)
01.02.1999, 28.02.1999, 01.01.1999, 31.01.1999
8 июн 19, 17:21    [21905189]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
sdku,

Спасибо, здесь тоже был три дня назад, даже пример скачал для изучения ))
8 июн 19, 17:29    [21905191]     Ответить | Цитировать Сообщить модератору
 Re: К сумме за текущий месяц прибавить сумму за предыдущий  [new]
kniga
Member

Откуда: KZ
Сообщений: 113
sdku, спасибо за код!
8 июн 19, 17:33    [21905194]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft Access Ответить