Sum(iif()) function problem

N

nelpet

Registered User.
Local time
Today, 08:14
Joined
Nov 25, 2011
Messages
22
Hi,
I have this select
SELECT
NOVI.GBR,
NOVI.AB,
Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm,
Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm,

(Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA,

Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],0)) AS Gorivo,

Sum(NALOG1.KM) AS SumOfKM, Avg(IIf(([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/[NALOG1.KM])*100 AS potrosgor100km,
Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051"),[MAGACIN.KOL],Null)) AS Motmaslo,

Sum(IIf(([MAGACIN.SIFRA]="0992201"),[MAGACIN.KOL],Null)) AS Addblue,

((Sum(IIf(([MAGACIN.SIFRA]="0992201"),([MAGACIN.KOL]),Null)))/(Sum(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null))))*100 AS Addbluegor,

Sum(IIf(([MAGACIN.SIFRA]="0999001"),[MAGACIN.KOL],Null)) AS Antifriz,

NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA",""))) AS Zabeleska

FROM (NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR) INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR

GROUP BY NOVI.GBR, NOVI.AB, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))

HAVING (((NOVI.GBR)>="1002" And (NOVI.GBR)<="1080") AND ((NOVI.AB)="AK"));

but it doesn't return correct values for Gorivo. The sum is not correct
I want to have the sum for MAGACIN.KOL for MAGACIN.SIFRA='0991000' and MAGACIN.DATA to be betweem @data1 and @data2

Can anybody help me please?
Thanks


 
Yes, but I only solved the second part i.e. the second select.
So I made a new query with this sql
SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS SumOfKOL, MAGACIN.SIFRA
FROM MAGACIN
WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#))
GROUP BY MAGACIN.GBR, MAGACIN.SIFRA
HAVING (((MAGACIN.GBR)>='1002' And (MAGACIN.GBR)<='1080') AND ((MAGACIN.SIFRA)='0991000'));

and it gives me correct values for the quantity (which is KOL column).

But I want to implement this in the SUM(IIF ()) function that I have sent earlier.

How to embed this in the part for Gorivo in the sent sql statement? Now I have
Gorivo: Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) And ([MAGACIN.GBR]>=[@gbr1] And [MAGACIN.GBR]<=[@gbr2]);[MAGACIN.KOL];0))
which returns me much bigger number than expected.

Can anybody help me please?

Thanks
 
So go back to the thread, and based on the table of values you showed, tell us (in table format) what you would like it to be.
 
I have three tables and they are prety big. In the previous thread I posted only very little segment of the table MAGACIN, but the whole select contains data from all the tables.
Here is just short segment of the result of the query. It should look something like this at the end, when running the select, but with correct values for the sum functions:
Query1 GBR AB pockm krajkm RAZLIKA Gorivo SumOfKM potrosgor100km Motmaslo Addblue Addbluegor Antifriz DATAP DATAS Zabeleska 1002 AK 45830 48556 2726 40868 878285 152,975685142736 3842 13838 1,86963112683173


ZA SERVIS 1004 AK 44362 48812 4450 121905 1437912,59999999 134,55978137143 6489 22239 1,67473194800266


ZA SERVIS 1006 AK 4618 50797 46179 92000 1626464,00000001 90,013010558669 4900 20950 2,01723556882192


ZA SERVIS 1008 AK 5493 54993 49500 279832 3187659,59999999 98,4100323426746 6688 37848 2,2602459946444


ZA SERVIS 1010 AK 41686 43752 2066 47000 845324,200000003 183,724398056591 3120 14600 1,93952919921356


ZA SERVIS 1012 AK 43075 48296 5221 169400 1602755 158,866924758418 7000 22400 1,51057401812689


ZA SERVIS 1014 AK 19056 22813 3757 25955 519721 99,3505470353813 1189 5597 2,02263676378118


ZA SERVIS 1016 AK 31678 33938 2260 32425 594526,1 108,696713101634 1550 6500 1,77123782273997


ZA SERVIS 1018 AK 3207 33962 30755 45200 6344050,79999998 118,338809745045 2880 13360 2,39100866203737


ZA SERVIS 1020 AK 31389 34364 2975 56545 755125,599999995 220,116710458734 2236 13889 2,15189873417722


ZA SERVIS 1022 AK 32215 38699 6484 219300 1633071 117,808372899451 4275 29700 2,22085132634176


ZA SERVIS 1024 AK 20608 30485 9877 75670 792807,100000007 81,7031278219421 230 6808 1,44305772230889


ZA SERVIS 1026 AK 1130 31484 30354 119259 1146596 109,608556211252 4410 20727 2,44192087879463


ZA SERVIS 1028 AK 4597 38853 34256 167416 1670539 89,9158066190456 3536 25908 2,32005845816588


ZA SERVIS 1030 AK 30996 35899 4903 116022 1104282 110,804341580596 3782 17690 1,93114470267031


ZA SERVIS
This is a result of the query
SELECT NOVI.GBR, NOVI.AB, Min(NALOG1.DATA) AS MinOfDATA, Max(NALOG1.DATA) AS data2, Min(NALOG1.POCKM) AS MinOfPOCKM, Max(NALOG1.KRAJKM) AS MaxOfKRAJKM, (Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA, Count(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null)) AS Gorivo, Avg(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null)/100) AS potrosgor100km, Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051"),[MAGACIN.KOL],Null)) AS Motmaslo, Sum(IIf(([MAGACIN.SIFRA]="0992201"),[MAGACIN.KOL],Null)) AS Addblue, ([Addblue]/[Gorivo])*100 AS Addbluegor, Sum(IIf(([MAGACIN.SIFRA]="0999001"),[MAGACIN.KOL],Null)) AS Antifriz, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA",""))) AS Zabeleska
FROM (NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR) INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
GROUP BY NOVI.GBR, NOVI.AB, ([Addblue]/[Gorivo])*100, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))
HAVING (((NOVI.GBR)>="1082" And (NOVI.GBR)<="1149") AND ((NOVI.AB)="AK") AND ((Min(NALOG1.DATA))>=#10/1/2011#) AND ((Max(NALOG1.DATA))<=#10/31/2011#));


but the sum is not true. it is about 30 times more then the real value.
 
Last edited:
Oh my, too many spaces. Can you edit your post and line them up nicely. Just like you did in the other thread.

Also I need to see field names so perhaps use "|" as the delimeter or wrap it in Code tags.
 
Here is the biggining part of the result of the select:


untitled1.jpg

And here is the select:
SELECT NOVI.GBR, NOVI.AB, Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm, Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm, (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA, Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],0)) AS Gorivo, Sum(NALOG1.KM) AS SumOfKM, Avg(IIf(([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/[NALOG1.KM])*100 AS potrosgor100km, Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Motmaslo, Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Addblue, ((Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null))))*100 AS Addbluegor, Sum(IIf(([MAGACIN.SIFRA]="0999001") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Antifriz, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA",""))) AS Zabeleska
FROM (NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR) INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
GROUP BY NOVI.GBR, NOVI.AB, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))
HAVING (((NOVI.GBR)>="1002" And (NOVI.GBR)<="1080") AND ((NOVI.AB)="AK"));


And here is the table MAGACIN only with values for gbr="1002" date between 01.10.2011 and 31.10.2011, and sifra="091000".


untitled2.JPG
​
So, the sum of Gorivo, and all the columns where I have sum are not correct.
Thanks
 
Last edited:
I don't think you are following me. I need to see what you would like it to be, not what isn't working.
 
As in the second table, I have posted as untitled1.jpg, the sum for Gorivo should be 1202 (it is the sum of column KOL), not 40868 as in my case, in the first table shown in untitled.jpg. So is the case with all other columns where I have sum.
Thanks
 
You do realise that I don't speak your language right? ;) What is CENA, BRMAG and BRNAL? And how should they be calculated?
 
From that table only KOL should be calculated. So, CENA,BRMAG and BRNAL are not important so, this is important,
Untitled1.jpg
this table and the calculation of KOL, depending on SIFRA, DATA, and GBR as I have posted earlier
9Hj42LgDWoZAAAAABJRU5ErkJggg==
 
I'm looking at the data in your other thread and comparing it with the screenshot and the DATA field doesn't match. Can you fix this.
 
Here are the both tables. The Gorivo column in the first table which I have made using the posted select statement is Sum(KOL). Kol is the column that should be calculated with sum() in the table MAGACIN which I use in the select.
Untitled.jpg
Untitled1.jpg
 
Ok, we are really wasting each other's time here. Look at the records you posted in your other thread (post #1) and look at what you've just posted (Untitled.jpg), they are not the same.

Also compare Untitled.jpg and Untitled1.jpg, how would you understand what you're doing when the fields are not the same and they are lots of irrelevant fields in the image.

I will re-iterate for the last time what would be useful:

A screenshot of records that look like your other thread but with values that match with Untitled1.jpg. I want to be able to look at your current table and your expected result and be able to map both.

We've been on this for far too long and I think it's time you get it right this time.
 
The first post from with this title and the one with the images are completely the same and give the same results. And I use the second table Magacin to make the first table using the posted select statement. If you are looking at http://www.access-programmers.co.uk/...d.php?t=218674, don't watch it please since this is a new post.
 
Alrighty. Let's forget about that thread, but Untitled.jpg has lots of irrelevant fields that I don't need to see. Can you post a screenshot of the table with ONLY the relevant fields that match with Untitled1.jpg
 
You must log in or register to reply here.

Similar threads

N
Combining selects in one select in access query
Replies
4
Views
936
Jeanette Cunningham
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom