Report Question - Counting & Grouping

[Vegboy]

First of all, we have a database for all company customer cases (through out the whole year of 2006). Every case has its own priority level.

‘Priority’ column has three option, ‘High’, ‘Medium’, and ‘Low’.

We are new to Access, and we are trying group all the case month to month (group them by their created date) on separate pages. At the end of each monthly summary, we’d like to do a number count on ‘High’, ‘Medium’, and ‘Low’.

Here is a sample that we are trying to get to…

Sample Datebase,
Case #// Created Date// Priority Level
001 12/5/06 High
002 12/7/06 Low
003 12/3/06 High
004 12/1/06 Medium
005 11/9/06 Medium

Sample Report we are trying to get to...
Summary for December:
Case #// Created Date// Priority Level
001 12/5/06 High
002 12/7/06 Low
003 12/3/06 High
004 12/1/06 Medium

Total Case: 4
High Priority Case: 2
Medium Priority Case: 1
Low Priority Case: 1

your help will be very appreciated!

 

[Moniker]

To separate the dates by month, use the Month function with a group by, like this:

SELECT Month(Your_Date_Field) AS Date_Month FROM Your_Table GROUP BY Month(Your_Date_Field)

You'll probably want to separate out the year as well. (Hint: There's probably a Year function. )

To count the number of cases per month, you would combine a count and a group by, like this:

SELECT Month([Your_Date_Field]) AS Date_Month, Count(Month([Your_Date_Field])) AS Date_Month_Count
FROM Your_Table
GROUP BY Month([Your_Date_Field]);

Note that you only group by the selected field, not the counted field. That's because COUNT is an aggregate function.

Play around with this a little bit, and it should hit you that using a GROUP BY on the Priority field will provide that separation as well. (To turn on aggregate queries like this, click the Sigma icon on the toolbar in the query design view window.)

~Moniker