Calculate the percentage per passengers in the query

[barkarlo]

To calculate the percentage of the number of passengers in the total number of passengers per single order using query:

[COLOR=#333333][FONT=Arial]SELECT[/FONT][/COLOR][/SIZE][/FONT][SIZE=3][COLOR=#333333][FONT=Arial] A.Date, A.Plate, A.Pax, A.Agency, A.Code, IIf ([pax]> 0, Round ([Pax] / (SELECT Sum (Pax) FROM Sheet2 as B WHERE B.Date = A.Date and B.code = A.code), 2), 1) AS KOR[/FONT][/COLOR][/SIZE]
[SIZE=3][FONT=Arial][COLOR=#333333]FROM Sheet2 AS A[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Arial][COLOR=#333333]ORDER BY A.Date; [/COLOR][/FONT][/SIZE]

.
Query works fine in all cases except one, and that is
when the encounter a same order in one day
01/04/2013. D_1 0pax
01/04/2013. D_1 5pax
in this case the formula gives a coefficient of 1 to 0 passengers and coefficient of 1 to 5 passengers, should be given a score of 0 0 of passengers and 1 to 5 passengers

 

[CJ_London]

I think you need to chage your iif statement slightly

IIf ([pax]> 0, Round ([Pax] / (SELECT Sum (Pax) FROM Sheet2 as B WHERE B.Date = A.Date and B.code = A.code), 2), 0)