Group by Month in Query

[Guest]

I'm trying to take information from a table that has defect information for
for each part produced and the date reported. I want to take this data and
summarize by month the number of defects per month. The difficulty I'm
finding is that if I group by the date in a query it groups by the individual
day not the month. I've also used the following expression: "Month:
Format$([Final FPY Table].Date,'yyyy-mm')" which works okay but then when I
go to the graph the data it does not recognize this as a date which creates
additional difficulties. Thanks for your help.

 

[Tom Ellison]

Dear Josh:

Generally, it works well to both GROUP and SORT by Year/Month using:

Year([Date]) * 12 + Month([Date])

This assigns a unique integer value to each month. You could also

GROUP BY Year([Date]), Month([Date])

but the first solution is also useful for correct sorting.

Tom Ellison

 

[Guest]

I'm not sure that this helped me a lot but it got me pointed in the right
direction. I kept the following: "Month: Format$([Final FPY
Table].Date,'yyyy-mm')" which essentially does the same thing as you're first
suggestion. Then I added another column that records the last date in each
months data. This way access recognizes this as a date and I can drop off
the day and only display the month and year in my report. Thanks for the
help.

Dear Josh:

Generally, it works well to both GROUP and SORT by Year/Month using:

Year([Date]) * 12 + Month([Date])

This assigns a unique integer value to each month. You could also

GROUP BY Year([Date]), Month([Date])

but the first solution is also useful for correct sorting.

Tom Ellison

I'm trying to take information from a table that has defect information
for
for each part produced and the date reported. I want to take this data
and
summarize by month the number of defects per month. The difficulty I'm
finding is that if I group by the date in a query it groups by the
individual
day not the month. I've also used the following expression: "Month:
Format$([Final FPY Table].Date,'yyyy-mm')" which works okay but then when
I
go to the graph the data it does not recognize this as a date which
creates
additional difficulties. Thanks for your help.