Problem with the sum function in quey select

[nelpet]

here is my table MAGACIN

GBR DATA SIFRA IME KOL
1001 18.10.2011 0991000 DIZEL GORIVO 45
1001 18.10.2011 0991000 DIZEL GORIVO 55
1001 21.10.2011 0992201 AD-BLU 12
1001 22.10.2011 0991000 DIZEL GORIVO 140
1001 26.10.2011 0991000 DIZEL GORIVO 90
1001 27.10.2011 0991000 DIZEL GORIVO 120
1001 28.10.2011 0991000 DIZEL GORIVO 45
1001 29.10.2011 0991000 DIZEL GORIVO 85
1001 30.10.2011 0991000 DIZEL GORIVO 80
1001 31.10.2011 0009055 TRAKA IZOLIR 1
1001 31.10.2011 0009041 BOJA BELA (AVTO) 1
1001 31.10.2011 0037005 SMIRGLA NA PLATNO 3
1001 31.10.2011 0009049 RAZREZIVAC AVTO 1
1001 31.10.2011 0037042 SMIRGLA VODENA 3
1002 19.02.2011 0991000 DIZEL GORIVO 8
1002 22.02.2011 0991000 DIZEL GORIVO 10
1002 23.02.2011 0991000 DIZEL GORIVO 110
1002 25.02.2011 0991000 DIZEL GORIVO 60
1002 25.02.2011 0991000 DIZEL GORIVO 50
1002 26.02.2011 0991000 DIZEL GORIVO 130
1002 28.02.2011 0991000 DIZEL GORIVO 125
1002 01.03.2011 0991000 DIZEL GORIVO 65
1002 01.03.2011 0991000 DIZEL GORIVO 40
1002 02.03.2011 0991000 DIZEL GORIVO 100
1002 03.03.2011 0991000 DIZEL GORIVO 115

and I want to sum the KOL by GBR, Data>=10.02.2011 and Data<=31.10.2011, SIFRA='0991000'.

For example
1002 19.02.2011 28.02.2011 0991000 DIZEL GORIVO 368
1001 18.10.2011 31.10.2011 0991000 DIZEL GORIVO 681

I want to have one row for every GBR for that time period and SUM(KOL),
can anybody help me please?

Thank you

 

[vbaInet]

Ok, the part in blue is not logical.

1001 18.10.2011 31.10.2011 0991000 DIZEL GORIVO 681

There are some values like TRAKA IZOLIR, SMIRGLA NA PLATNO etc but you've added them all up to make 681.

Re-think your approach.

 

[nelpet]

ok, let's analyse this
SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS SumOfKOL
FROM MAGACIN
WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#))
GROUP BY MAGACIN.GBR
HAVING (((MAGACIN.GBR)>='1002' And (MAGACIN.GBR)<='1080'));


this returns 1202 in SumOfKol instead of 1241, for gbr=1002
999 in SumOfKol instead of 988, for gbr=1003 e.t.c

so the calculation is not correct??????

 

[nelpet]

It is also very strange that while calculation eventhough all the values in the KOL column are integer numbers, the sum calculates decimal number?? very strange

 

[Brianwarnock]

ok, let's analyse this
SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS SumOfKOL
FROM MAGACIN
WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#))
GROUP BY MAGACIN.GBR
HAVING (((MAGACIN.GBR)>='1002' And (MAGACIN.GBR)<='1080'));


this returns 1202 in SumOfKol instead of 1241, for gbr=1002
999 in SumOfKol instead of 988, for gbr=1003 e.t.c

so the calculation is not correct??????

We cannot comment as we do not have access to the data.

Further you have totally ignored vbaInet's post.

Brian

 

[nelpet]

I didn't ignore it but the problem is that whichever fields I choose, even if I chose three columns in the select, (the number of the vehicle and the quantity and the date) Sum(quantity) returns decimal, eventhough all the values in the column for quantities are integer numbers???

SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS SumOfKOL
FROM MAGACIN
WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#))
GROUP BY MAGACIN.GBR
HAVING (((MAGACIN.GBR)='1002'));


how is it possible from integer to summarise decimal? It's confusing me. Thanks

 

[nelpet]

I solved it

 

[vbaInet]

I solved it

I hope you are getting the right results.