Grouping data by Month 1

[Ryath]

Hi all,

Got a quickey question for you all currently i have data that looks like this:-

Date Hours
1/5/99 21
2/5/99 41
18/5/99 15
21/8/99 77
31/8/99 4
etc etc

So as observed the hours can be varied from month to month, and some months...ie june and july in the example are missing. How can i group the data by month to give me a total per month, but also saying that june and july has 0 hours?

Thx all
Will

 

[dalchri]

I would attempt a cross tab query as follows:
[tt]
TRANSFORM IIf(SUM(Hours) Is Null,SUM(Hours),0)
SELECT Null
FROM YourTable
GROUP BY Null
PIVOT Month([Date]) IN (5,6,7,8)
[/tt]
The in clause makes certain that there is a column for each month June through August. I had to put the SELECT Null and GROUP BY Null clauses in to make access happy. These statements just result in an extra, useless field.

 

[Ryath]

Thx, it works.... the dreaded but: is there a way to get it in column format so the date is in the column rather than a row? Will

 

[dalchri]

Unfortunately, I don't know of a way to do this and have the months June and July present unless either you have dates in the table, or you have a separate reference table that lists all the months in the year. For example, you could left join from a table as follows:
[tt]
tblMonth
--------
Month Text(10)
January
February
etc...
[/tt]