Hi,
I use MS Access database as a database for my C# windows application. I want to get one row for every date as a result from my queries, instead of several. In my C# application I made this command strings with which I fill the dataset tables:
comstring1 =
"SELECT MAGACIN.GBR, MAGACIN.DATA, NOVI.AB, IIf([sifra]='0992201',[MAGACIN.KOL],Null) AS Addblue, IIf([sifra]='0999001',[MAGACIN.KOL],Null) AS Antifriz, IIf([sifra]='0991000',[MAGACIN.KOL],Null) AS Gorivo, IIf([sifra]='0993050',[MAGACIN.KOL],Null) AS Motaslo, (IIf([sifra]='0992201',[MAGACIN.KOL],Null)/IIf([sifra]='0991000',[MAGACIN.KOL],Null))*100 AS Addbluegor FROM MAGACIN INNER JOIN NOVI ON MAGACIN.GBR = NOVI.GBR WHERE (((MAGACIN.GBR)=[@gbr1]) AND ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((MAGACIN.DATA)>=[@data1] And (MAGACIN.DATA)<=[@data2]) )ORDER BY MAGACIN.DATA";
comstring2 =
"SELECT NALOG1.GBRV, NALOG1.DATA, Min(IIf(([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm, Max([NALOG1.KRAJKM]) AS krajkm, Max([NALOG1.KRAJKM])-Min(IIf(([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS RAZLIKA, Max(NOVI.DATAP) AS Poslprov, Max(NOVI.KMP1) AS KMP, Max(NOVI.DATAS) AS Poslserv, Max(NOVI.KMS1) AS KMS, Sum(NALOG1.km) AS SUMKM, IIf(Sum(NALOG1.km)>=(([NOVI].[KMS1]+[NOVI].[KMS2])-1000),'ZA SERVIS',(IIf(Sum(NALOG1.KM)>=(([NOVI].[KMP1]+[NOVI].[KMP2])-1000),'PROVERKA',' '))) AS Zabeleska FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR GROUP BY NALOG1.GBRV, NALOG1.DATA, NOVI.KMP1, NOVI.KMP2, NOVI.KMS1, NOVI.KMS2, NOVI.AB HAVING (((NALOG1.GBRV)=[@gbr1])and ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((NALOG1.DATA)>=[@data1] And (NALOG1.DATA)<=[@data2]))";
When I run the strings as queries in Access, I get what I want, correct result, i.e. one row for every date, but when I use the strings for filling the dataset, I get several rows for one date, which is not what I am aiming at. I.e. I want to get min of pockm and max of krajkm for every date of the range in one row.
Ex. for the date 08.10.2011 I have:
GBRV DATA pockm krajkm Razlika MaxOfDATAP MaxOfDATAS
1001 08.10.2011 43394 43477 83 27.09.2011
1001 08.10.2011 43477 43580 103 27.09.2011
and I want a result:
GBRV DATA pockm krajkm Razlika MaxOfDATAP MaxOfDATAS
1001 08.10.2011 43394 43580 186 27.09.2011
Could anybody help me please?
Thanks in advance