Problem trying to figure out monthly queries

[hammerva]

Hello all

I am trying to create a query from my account of the number of defects we have had in a month. I know the date to use (Discovered On). But I need to show the sum of defects by month and it in a format of "mon-yy". This data will be used in a chart for a later process.

I figure I need to pull the Discovered on Date field but to not show it. And then create a date expression and group by that. But can't seem to figure out how to do that.

Any ideas or how I can pull this off.

Thanks plenty

 

[indyaries]

hammerva,

Create a new query based on the table with your data.

On the field line of a query column, paste this expression:
Date By Month: Format([Discovered On],"mmm yy")

Add the field containing the defects. Click the SIGMA button on the query toolbar, select COUNT for your defects, and GROUP BY on the date field.

HTH,

Bob in Indy

 

[hammerva]

Sweet that worked great. Thanks. But if I can I have another question.

How can I sort this by the month. Right now it is sorted in alphabetic order by the month. I tried the following statement:

ORDER BY Year([Defects].[Discovered_On]) & DatePart("m", [Defects].[Discovered_On] )

I thought it would show order like 200101, 200102, etc. but it isn't working. Right now it is display like 'Jan 01',' Oct 01','Dec 01','Jun 01', etc.

Any ideas what is wrong with this ORDER BY step?

Anyway thanks again.