Добрый день, уважаемые форумчане
Помогите решить проблему суммирования поля [PAX] в запросе с условием, что
1)[EventDate], 2)[EventTime], 3)[EID] одинаковы (нужно чтобы сумма PAX из нескольких заказов Orders.OID, если такие существуют, показывалась в ячейке Sum Of Pax, на каждый заказ сумма нескольких заказов
SELECT Program.EventDate, Program.EventTime, Orders.OID, Program.EID, Events.EventType,
Program.Pax, Events.EventFrom, Events.EventTo, EventsQuotations.From, EventsQuotations.To, EventsQuotations.[1 P],
EventsQuotations.[2 P], EventsQuotations.[3-5 Pax], EventsQuotations.[6-8 Pax], EventsQuotations.[9-12 Pax], EventsQuotations.[13-19 Pax],
EventsQuotations.[20-29 Pax],
EventsQuotations.[30-up Pax], EventsQuotations.['Join'], IIf([Program]![Pax]=1,[1 P]) AS 1P, IIf([Program]![Pax]=2,[2 P]) AS 2P, IIf([Program]![Pax]=3,[3-5 Pax]) & IIf([Program]![Pax]=4,[3-5 Pax]) &
IIf([Program]![Pax]=5,[3-5 Pax]) AS [3-5P], IIf([Program]![Pax]=6,[6-8 Pax]) & IIf([Program]![Pax]=7,[6-8 Pax]) & IIf([Program]![Pax]=8,[6-8 Pax]) AS [6-8P], IIf([Program]![Pax]=9,[9-12 Pax]) &
IIf([Program]![Pax]=10,[9-12 Pax]) & IIf([Program]![Pax]=11,[9-12 Pax]) & IIf([Program]![Pax]=12,[9-12 Pax]) AS [9-12P], IIf([Program]![Pax]=13,[13-19 Pax]) & IIf([Program]![Pax]=14,[13-19 Pax]) &
IIf([Program]![Pax]=15,[13-19 Pax]) & IIf([Program]![Pax]=16,[13-19 Pax]) &
IIf([Program]![Pax]=17,[13-19 Pax]) & IIf([Program]![Pax]=18,[13-19 Pax]) &
IIf([Program]![Pax]=19,[13-19 Pax]) AS [13-19P], IIf([Program]![Pax]=20,[20-29 Pax]) &
IIf([Program]![Pax]=21,[20-29 Pax]) & IIf([Program]![Pax]=22,[20-29 Pax]) &
IIf([Program]![Pax]=23,[20-29 Pax]) & IIf([Program]![Pax]=24,[20-29 Pax]) &
IIf([Program]![Pax]=25,[20-29 Pax]) & IIf([Program]![Pax]=26,[20-29 Pax]) &
IIf([Program]![Pax]=27,[20-29 Pax]) & IIf([Program]![Pax]=28,[20-29 Pax]) &
IIf([Program]![Pax]=29,[20-29 Pax]) AS [20-29P], IIf([Program]![Pax]>=30,[30-up Pax]) AS ['30-up Pax'],
IIf([Program]![Pax]<0,['Join']) AS [Join], Events.EventType AS Descr, ([EventFrom]) & ' => ' & ([EventTo]) AS Period, Program.Pax AS Quantity, 1 AS [In House], IIf([1P]>0,[1P]) &
IIf([2P]>0,[2P]) & IIf([3-5P]>0,[3-5P]) & IIf([6-8P]>0,[6-8P]) &
IIf([9-12P]>0,[9-12P]) & IIf([13-19P]>0,[13-19P]) & IIf([20-29P]>0,[20-29P]) &
IIf(['30-up Pax']>0,['30-up Pax']) AS Price, (([Price])*[In House]*[Quantity]) AS Price_Total, [Descr] & ' / ' & [EventDate] & ' / ' & [Period] AS Det, Orders.PAX AS [Sum Of Pax]
FROM Orders INNER JOIN ((Events INNER JOIN Program ON Events.EID = Program.EID) INNER JOIN EventsQuotations ON Events.EID = EventsQuotations.EID) ON Orders.OID = Program.OID
WHERE (((Events.EventType)="Transfer") AND ((EventsQuotations.From)<[EventDate]) AND ((EventsQuotations.To)>[EventDate]) AND ((Program.Group)=True) AND ((Orders.OrderStatus)=2))
ORDER BY Program.EventDate, Orders.OID;
Заранее спасибо за помощь