Group by Month in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joshroberts - 02 May 2006 18:16 GMT

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.

Reply to this Message

Tom Ellison - 02 May 2006 18:28 GMT

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
[quoted text clipped - 9 lines]
> creates
> additional difficulties. Thanks for your help.

Reply to this Message

joshroberts - 02 May 2006 19:11 GMT

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:
>
[quoted text clipped - 23 lines]
> > creates
> > additional difficulties. Thanks for your help.

Reply to this Message