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 these command strings with which I fill the dataset tables:
comstring1=
"SELECT MAGACIN.GBR, MAGACIN.DATA, NOVI.AB, Sum(IIf([sifra]='0992201',[kol],Null)) AS Addblue,Sum(IIf([sifra]='0999001',[kol],Null)) AS Antifriz, Sum(IIf([sifra]='0991000',[kol],Null)) AS Gorivo, Sum(IIf([sifra]='0993050',[kol],Null)) AS Motmaslo, Sum(IIf([sifra]='0992201',[kol],Null))/Sum(IIf([sifra]='0991000',[kol],Null))*100 AS Addbluegorivo FROM MAGACIN INNER JOIN NOVI ON MAGACIN.GBR = NOVI.GBR GROUP BY MAGACIN.GBR, MAGACIN.DATA, NOVI.AB HAVING (((MAGACIN.GBR)=[@gbr1]) AND ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((MAGACIN.DATA)>=[data1] And (MAGACIN.DATA)<=[data2])) ORDER BY MAGACIN.DATA";
the resul for the date>=10.10.20111 and date<=13.10.2011 is
GBR |
DATA |
AB |
Addblue |
Antifriz |
Gorivo |
Motmaslo |
Addbluegorivo |
|---|
1001 |
10.10.2011 |
GP |
|
|
115 |
|
|
1001 |
11.10.2011 |
GP |
|
|
40 |
|
|
1001 |
12.10.2011 |
GP |
12 |
|
110 |
|
10,9090909090909 |
1001 |
13.10.2011 |
GP |
12 |
|
125 |
|
9,6 |
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]))";
the result from the second query is
GBRV |
DATA |
pockm |
krajkm |
Razlika |
MaxOfDATAP |
MaxOfDATAS |
sumkm |
MaxOfKMP1 |
MaxOfKMS1 |
|---|
1001 |
10.10.2011 |
43580 |
43867 |
287 |
|
27.09.2011 |
287 |
41400 |
41400 |
1001 |
11.10.2011 |
43867 |
43967 |
100 |
|
27.09.2011 |
100 |
41400 |
41400 |
1001 |
12.10.2011 |
43967 |
44254 |
287 |
|
27.09.2011 |
287 |
41400 |
41400 |
1001 |
13.10.2011 |
44254 |
44541 |
287 |
|
27.09.2011 |
287 |
41400 |
41400 |
in the C# windows application I have:
......
command1.CommandText = comstring1;
command2.CommandText = comstring2;
......
oleDBDataAdapter1.SelectCommand = command1;
oleDBDataAdapter1.Fill(dataSet2, "baraniotselect")
oleDBDataAdapter1.SelectCommand = command2;
oleDBDataAdapter1.Fill(dataSet2, "PocKrajRazl1");
When I run the strings separatelly as queries in Access, I get what I want (for each query), correct result, i.e. one row for every date, but when I use the strings for filling two tables in the dataset, I get several rows for one date, which is not what I am aiming at. I.e. I get several rows for one date with different values for addblue, antifriz, Motmaslo, Addbluegor, gorivo and I want to get for every date of the range a single rows with values for the fields in one row. In other words, I want just to merge both tables which I get as a result from the queries above.
Could anybody help me please?
Thanks in advance