Counting in queries

[Guest]

I have a database to record incidents (OH&S stuff). I have a table that
records the incident date type of incident (fall, trip etc), the location,
and whether the person was a resident, visitor or staff member.

I am having trouble creating reports/queries to analyse the information.
What I am looking to do is produce a report that shows how many incidents of
each type occurred over a period of time and the split between resident,
staff and visitor for each incident type.

I have created a query from my table that show the total number of incidents
of each type for each type of person (visitor, staff, resident), using COUNT.
But what I can't do is select a time frame for the query to tally - ideally
this report would be done monthly so that I can report on the results.

Can anybody offer any suggestions?? (Using Access2003)

Thanks and regards
Sophie

 

[Gary Walter]

I have a database to record incidents (OH&S stuff). I have a table that
records the incident date type of incident (fall, trip etc), the location,
and whether the person was a resident, visitor or staff member.

I am having trouble creating reports/queries to analyse the information.
What I am looking to do is produce a report that shows how many incidents
of
each type occurred over a period of time and the split between resident,
staff and visitor for each incident type.

I have created a query from my table that show the total number of
incidents
of each type for each type of person (visitor, staff, resident), using
COUNT.
But what I can't do is select a time frame for the query to tally -
ideally
this report would be done monthly so that I can report on the results.

It sounds like you have a totals query and would
like help limiting query to a time frame.

In query design, this time frame can be determined by
what you enter into "Criteria:" row of the grid under
column for your date field...

Field: IncidentDate
Table:
Total: Where
Sort:
Show:
Criteria:
or:

(Note that "Total:" row is set to "Where")

When setting this Criteria, the function
DateSerial() is your best friend...

The following are examples for what you
would type in the Criteria row (all on one line)
for some common time frames...

-------------
MTD query
-------------

1) if your date field does not contain time portion...

BETWEEN DateSerial(Year(Date()), Month(Date()), 1)
AND DateSerial(Year(Date()), Month(Date()) + 1, 0)

2) if your date field does contain a time portion...

= DateSerial(Year(Date()), Month(Date()), 1)

AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

----------------------
Previous Month query
----------------------

1) if your date field does not contain time portion...

BETWEEN DateSerial(Year(Date()), Month(Date())-1, 1)
AND DateSerial(Year(Date()), Month(Date()), 0)

2) if your date field does contain a time portion...

= DateSerial(Year(Date()), Month(Date())-1, 1)

AND < DateSerial(Year(Date()), Month(Date()), 1)

------------
YTD query
-------------

1) if your date field does not contain time portion...

BETWEEN DateSerial(Year(Date()), 1, 1)
AND Date()

2) if your date field does contain a time portion...

= DateSerial(Year(Date()), 1, 1)

AND < Date() + 1

----------------------
Previous Year query
----------------------

1) if your date field does not contain time portion...

BETWEEN DateSerial(Year(Date())-1, 1, 1)
AND DateSerial(Year(Date()), 1, 0)

2) if your date field does contain a time portion...

= DateSerial(Year(Date())-1, 1, 1)

AND < DateSerial(Year(Date()), 1, 1)

 

[John Spencer]

It sounds as if you are using a totals query.

If you are using the query grid, then add your date field to the grid
-- Change GROUP BY to WHERE in the totals row
-- Enter your date range in the criteria (Between #1/1/2006# and #1/31/2006#
to get January of 2006)

If that doesn't help, post the SQL of your query (Hint: View: SQL in the
menubar). Also tell us the name of the date field, so someone can suggest
the needed modification to your query.