What does "Where" mean (drop-down option under "Group By")?

[Amanda]

Hi, I can really use some help here...

I am working on a database for the risk management department that tracks
"Incident Reports" - including details of the incident, category of incident,
date & time, branch where it happened, etc.

I need to produce a report that displays for a _particular month and branch_
the number of incidents in each category. I can produce for each branch the
number of incidents in each category just fine when considering the entire
database (leaving the "Date" field out of the query entirely), but when I try
to limit it by month (using the "Date" field with the criteria "Between...")
it then lists each incidence of each category as a separate line item because
"Group By" is selected under the Date field. I have tried playing with the
other options for the Totals line item, and most of them produce incorrect
counts or do not work at all. If I change "Group By" to "Where" the totals
seem to match up to what I've counted by hand, but since I don't know what
"Where" actually does I don't know if it will continue to be accurate.

I hope this makes sense... Looking forward to your insights.

 

[Douglas J. Steele]

WHERE means you're setting a criteria, as opposed to using the field in the
GROUP BY.

 

[Marshall Barton]

Hi, I can really use some help here...

I am working on a database for the risk management department that tracks
"Incident Reports" - including details of the incident, category of incident,
date & time, branch where it happened, etc.

I need to produce a report that displays for a _particular month and branch_
the number of incidents in each category. I can produce for each branch the
number of incidents in each category just fine when considering the entire
database (leaving the "Date" field out of the query entirely), but when I try
to limit it by month (using the "Date" field with the criteria "Between...")
it then lists each incidence of each category as a separate line item because
"Group By" is selected under the Date field. I have tried playing with the
other options for the Totals line item, and most of them produce incorrect
counts or do not work at all. If I change "Group By" to "Where" the totals
seem to match up to what I've counted by hand, but since I don't know what
"Where" actually does I don't know if it will continue to be accurate.

Grouping on the date field will group on each separate date.
If you want to group by the month, try setting Group On to
Month.

 

[Marshall Barton]

I am working on a database for the risk management department that tracks
"Incident Reports" - including details of the incident, category of incident,
date & time, branch where it happened, etc.

I need to produce a report that displays for a _particular month and branch_
the number of incidents in each category. I can produce for each branch the
number of incidents in each category just fine when considering the entire
database (leaving the "Date" field out of the query entirely), but when I try
to limit it by month (using the "Date" field with the criteria "Between...")
it then lists each incidence of each category as a separate line item because
"Group By" is selected under the Date field. I have tried playing with the
other options for the Totals line item, and most of them produce incorrect
counts or do not work at all. If I change "Group By" to "Where" the totals
seem to match up to what I've counted by hand, but since I don't know what
"Where" actually does I don't know if it will continue to be accurate.

Sorry, I misread your question. I thought you were using
Sorting and Grouping in the report.

To group a query on months, use a calculated field with

MonthDate: Format(datefield, "yyyymm")

and set its Totals row to Group By

Under your date field, set its Totals row to Where

 

[John Spencer]

"WHERE" means that you are going to filter the records based on the criteria
BEFORE you aggregate the records.

If you use criteria under any other of the choices (Group By, Sum, Avg) in
means the results will be filtered after the aggregation takes place.

From your description, your query should work correctly.

If you wanted to get the values for several months at one time you might
have two columns using the date and time. One column grouping on the year
and month of the datefield and another column using where to limit the time
frame

Field: YearAndMonth: Format([YourDateField],"YYYY MM")
Total: Group By
Criteria: <BLANK>

Field: YourDateField
Total: WHERE
Criteria: Between #2007-09-01# and #2007-12-31#

So first the query would get all records for the specified time frame. Then
it would aggregate (combine) the records so you would get monthly totals.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Amanda via AccessMonster.com]

Hi everyone -

I thank you all for the advice. Sounds like what I did by "guess and check"
was the right thing after all.

Thanks for the help!!

-Amanda