Summing up according to date and Nr. fields 2

[manf01]

Hi All,

I need a help here, it looks easier but i got stuck here.


For example i have a table(actually its a large data table) with project numbers between 10
and 14 which were entered on different dates with different Amount of Charges.

ID Project_Nr Charges Date

1 10 130,00 01.01.2005
2 10 120,00 04.01.2005
3 10 100,00 10.01.2005
4 11 110,00 15.01.2005
5 11 90,00 20.01.2005
6 12 80,00 03.02.2005
7 12 140,00 15.02.2005
8 10 130,00 16.02.2005
9 10 120,00 19.02.2005
10 13 110,00 27.02.2005
11 13 90,00 02.03.2005
12 12 200,00 05.03.2005
13 12 120,00 09.03.2005
14 14 120,00 12.03.2005
15 14 150,00 23.03.2005



I want to get the end result like this ( if its possible ) [ by query ]


ProjectNr Jan(Charges) Feb March EachTotal Total

10 350 250 0 600 1810
11 200 0 0 200
12 0 220 320 540
13 0 110 90 200
14 0 0 270 270



Because after getting this i have to use this query result in Excel to compare with actual data
( In Excel the actual data is in above format )


Thanks a lot,

 

[PHV]

A starting point:
SELECT Project_Nr, Sum(IIf(Month([Date])=1,Charges,0)) As Jan, Sum(IIf(Month([Date])=2,Charges,0)) As Feb, Sum(IIf(Month([Date])=3,Charges,0)) As March, Sum(Charges) As EachTotal
FROM yourTable
WHERE [Date] Between #2005-01-31# And #2005-03-31#
AND Project_Nr Between 10 And 14
GROUP BY Project_Nr;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[manf01]

Thanks PHV,

I dont know why, but i am just getting an empty table, Same code is given as under: Can you please check it ? It will be a great help.


SELECT Calculation.Project_Nr,
Sum(IIf(Month([Date])=1,Charges,0)) AS Jan,
Sum(IIf(Month([Date])=2,Charges,0)) AS Feb,
Sum(IIf(Month([Date])=3,Charges,0)) AS March,
Sum(Calculation.Charges) AS EachTotal
FROM Calculation
WHERE (((Calculation.Date) Between "#31.01.2005#" And "#31.03.2005#")
AND ((Calculation.Project_Nr) Between "10" And "14"))
GROUP BY Calculation.Project_Nr;

 

[HarleyQuinn]

Hi,

Not trying to hi-jack this thread from PHV but I am assuming that your Date column is a Text column rather than a Date??

If so remove the # from the dates and it will return results. It might be worth storing the date field as a Date/Time data type for easier/more reliable calculations using dates.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[manf01]

Hi Harleyquinn,

It did helped me, Thanks a lot.

I want to get the whole Total as well.
Is it possible ?

Thanks,

 

[PHV]

You may try this (provided the Date and Project_Nr fields are defined as Text in the Calculation table):
SELECT Project_Nr,
Sum(IIf(Month(CDate([Date]))=1,Charges,0)) As Jan,
Sum(IIf(Month(CDate([Date]))=2,Charges,0)) As Feb,
Sum(IIf(Month(CDate([Date]))=3,Charges,0)) As March,
Sum(Charges) As EachTotal
FROM Calculation
WHERE CDate([Date]) Between #2005-01-31# And #2005-03-31#
AND Project_Nr Between '10' And '14'
GROUP BY Project_Nr;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[manf01]

Thanks a lot PHV.