Query to show averages by month

[DDEB]

I'm trying to setup a query that will return the average of a column by month. The table that I'm using for my query has a column called "Call Date" which is in the format dd/mm/yyyy, and I need it to stay in this format in the table. Right now when I run the query I'm getting the average of each column on a daily basis. Is there anyway to get the average of each column on a monthly basis?



Thanks!

[June7]

Need to extract the month/year by expression in a calculated field of query then use that field for the grouping.

MoYr: Format(Month([Call Date]),"00") & Year([Call Date])

[DDEB]

That worked, thanks!

I now need to create a parameter query so I can specify which month/year to pull the data for. The criteria I tried using was: Month([Call Date])=[Choose a month from 1-12] And Year([Call Date])=[Choose a year] however I got an error message that said "You tried to execute a query that does not include the specified expression ... as part of an aggregate function." I then tried substituting "MoYr: Format(Month([Call Date]),"00") & Year([Call Date])" for the "Call Date" in the criteria, which also gave me an error message. Any suggestions?

[June7]

I NEVER use query parameter prompts - can't validate user input. I have user enter critieria on form and query refers to control on form or use VBA to build WHERE argument of DoCmd.OpenForm (or OpenReport).

Show the full query statement for analysis. Needs to be something like:

SELECT Avg([data field to summarize]), Format(Month([Call Date]),"00") & Year([Call Date]) As MoYr FROM table WHERE Month([Call Date]),"00") & Year([Call Date]) = [Enter month 1-12] & [Enter Year as yyyy] GROUP BY Format(Month([Call Date]),"00") & Year([Call Date]);