Query that returns an item count??

[Guest]

I have a table of 400 records where each record represents a specific
incident (theft, injury, etc) that happened throughout an entire agency. (The
table also specifies the agency site at which the incident occurred, but
that's a different nightmare...) Each individual record specifies the type of
incident by an "incident code". I would like to create a make-table query
that will creat a table that lists one record for each incident code with two
fields 1) the incident code and 2) a count of the number of times that
incident code appears in my original table.

This will then be fed into a report. The report itself is far more complex
than this but I've been stumped on this first part for 2 days. Any help would
be appreciated.

Thanks,
YYY

 

[George Nicholson]

1) - Create a new query
- Select your table
- View>SQL View
- Paste the following (revising Field & Table names)
SELECT DISTINCT IncidentCode, Count(IncidentCode) AS IncidentCount
FROM MyTable
GROUP BY IncidentCode;

or

2) - Create a new query
- Select your table
- View>Properties to show the Properties window.
(If the Prop window it isn't showing "Query Properties", click in
the upper query design area (where tables go) until it does)
- QueryProperties: UniqueValues = Yes (This adds DISTINCT to the SQL)
- Add the IncidentCode field to the query grid TWICE
- View: make sure Totals is checked
- Under the 2nd IncidentCode, set the Total line to Count

In either case, once you get the results you want, change it to a Make table
query, send the results to Excel or Word, whatever you need to do.

(And, in the future, simply adding the AgencySite field would give you a
count of each IncidentCode at each Site....)

HTH,

 

[John Vinson]

I have a table of 400 records where each record represents a specific
incident (theft, injury, etc) that happened throughout an entire agency. (The
table also specifies the agency site at which the incident occurred, but
that's a different nightmare...) Each individual record specifies the type of
incident by an "incident code". I would like to create a make-table query
that will creat a table that lists one record for each incident code with two
fields 1) the incident code and 2) a count of the number of times that
incident code appears in my original table.

This will then be fed into a report. The report itself is far more complex
than this but I've been stumped on this first part for 2 days. Any help would
be appreciated.

Thanks,
YYY

You're making the common but erroneous assumption that you must have a
new Table in order to generate a new report. You don't.

Create a new Query based on your table. Make it a Totals query by
clicking the Greek Sigma icon - looks like a sideways M. Group BY
Incident Code, and select the table's primary key; change the totals
row operator on this field to Count.

You can then base a Report directly on this query. There's no need to
store the data redundantly in a table to do so. You can also join this
query to other queries (by Incident Code for example), or use it as
the recordsource for a Subreport.

John W. Vinson[MVP]

 

[Guest]

John,

Thanks so much. That did the trick! Now here's the next challege; working in
the same database I also need to get a count on the number of each particular
type of incident happens for each individual program. I've tried playing with
the method you suggested to solve my first problem and I can't get it to work
yet. Any help would be appreciated.

Thanks
YYY

 

[John Vinson]

Thanks so much. That did the trick! Now here's the next challege; working in
the same database I also need to get a count on the number of each particular
type of incident happens for each individual program. I've tried playing with
the method you suggested to solve my first problem and I can't get it to work
yet. Any help would be appreciated.

Group By both the program and the incident type, and count *.

Not knowing the exact structure of your tables I can't be more
specific, but if you could open your current query in SQL view and
post it here someone might be able to help.

John W. Vinson[MVP]