How do I display conut during a given period?

[egghi]

Hi,

I am creating a report which shows # of changes made for each customer account by account executive. I would like to be able to show the counts by week, month, quarter, or annual.

I created a query:

SELECT [UserName],[ChangeDate], Count[UserName] AS [Total # of Changes Made]
FROM ChangeAudit
GROUP BY [UserName], [ChangeDate];


But it only displays results by day by user. How do I group the results by week, month, quarter, or annual?

Thank you!!

 

[fredk]

You need to add additional fields to show the timeframe you want... for example, if you wanted to view by month, you would need to add month([changedate]) to add the month each record.

Then you would need to total by this field.

Hope that helps.

 

[egghi]

Thank you so much, fredk.

I tried but my query was wrong. Could you show me how to insert the date frame?

My query:

SELECT [UserName], [ChangeDate], Count([UserName]) AS [Total # of Changes Made]
FROM ChangeAudit
WHERE ([Changedate],"mm") IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
GROUP BY [dbo_ClickonAudit].[UserName], [dbo_ClickonAudit].[ChangeDate];

 

[dhookom]

I don't understand your query since [red]WHERE ([Changedate],"mm") [/red] is not a valid syntax. What type of field is ChangeDate?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]