Hi,
I use MS Access database. 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.
Could anybody help me please?
Thanks in advance
Loading
NelPosted Apr 30, 2012, 1:38 AM
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]))
(I don't know what did you mean by XXXX) and the result was the same. So I don't get one row for a single date, but several.
Kunal VaishyaPosted Apr 27, 2012, 7:13 AM
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) AS XXXXX ,
'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]))