Nel

Nel

  • NA
  • 716
  • 1.1m

Different results when running the queries in Access and in C# application

Apr 27 2012 7:04 AM
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


Answers (2)