Counting Query Results?

[benlaird24]

I have 4 different queries which return incidents that happen in different time spans during the day. They work fine. However, I want a query which will count how many records each of the previously mentioned 4 queries returned, and puts it in a pie chart or something like that. What do I need to do to just count the number of records the query returns?

 

[lscheer]

This all depends on how your query and/or your database is set up. If all the queries can be related to each other or to a master table via a key field, then you can probably set up some outer joins in a query that will count records from each query. This will probably only work if the time spans in each query are unique from the other queries (ie. there are no overlapping time values)

Do the queries need to be separate, or would it be possible to use a single query with criteria to return the different time spans all in one query? then you could build a query off of it and count on the different values. It's hard to say without more information about how they are set up.

In the simplest manner, you can look at the number each query returns in the record selector bar (at the bottom of the datasheet) and enter those values into Excel automatically.

 

[benlaird24]

I can incorporate all 4 queries into 1 if I need. Then in another query, I would need to make my criteria the time, but instead of printing the events that happen in a particular time frame, I want to be able to count the occurrences of each time frame. I know this is kind of vague, but any suggestions? Thanks for the help.

 

[lscheer]

You should just be able to run a total query on the first one. for instance:

Query1 has incidents and time associated with each one. You may have to set up a calculated field in this query to group the times or say, categorize them (e.g. Time1:[TimeValueField] where criteria is your first time span; Time2:[TimeValueField] Where your criteria is the second time span; all the way to Time4.) Then the query will return the records in those new "Time#" fields according to the time span criteria you entered. Save the query and create a new query based on Query1 with ONLY the fields pertinent to your graph/chart (Time# fields), otherwise the count function won't work properly. Click the Totals Query button (looks like greek letter sigma), and a new "Totals" row will appear in the design pane. In each of the Time# fields, select "Count". Save the second Query.

Therefore, if this were your recordset for Query1:
Incident1 Time1
Incident2 Time1

Incident3 Time2
Incident4 Time2
Incident5 Time2

Then Query2 should return results like this:
Count of Field Time1:
2
Count of Field Time2:
3

Then you can export the dataset into Excel and create a chart, or try to muscle around with the charting function in Access if you have time on your hands (and patience).

I hope this works, like I said, without seeing the dataset, it's hard to exactly tell how it would work best.

 

[benlaird24]

Thanks for the advice...One thing though. I was doing what you said, I had time1, time2, time3, and time4. They all look at my time field in my table. For each i have their specific criteria...
time1- time>4:00:00 PM and time<7:00:00 PM
time2- time>7:00:00 PM or time<6:00:00 AM (and doesnt seem to work for this one, but thats a diff. prob. Or works fine in my other query)
time3- time>6:00:00 AM and time<9:00:00 AM
time4- time>9:00:00 AM and time<4:00:00 PM

ok...this works when I have just time1 and its criteria in. However, put time2, time3, and time4 in with their criteria, and no records are found by the query. I think, "well, no time satisfies all 4 of those"...obviously, but is the query really checking all 4 criterium...just to test I change time2 to from time>7:00 PMto time>6:00 PM, and remove time3 and time4. So anytime from 6:00 PM to 7:00 PM should work, like i thought, all records in that span show.

How can I make the query work so it doesnt check the criteria for every field?
I hope everyone understands what I mean. Thanks for everything.

 

[Mile-O]

Put each criteria on a seperate line and you'll get a series of OR statements instead of AND statements.

 

[lscheer]

Also, if it's a consideration for you, if you have any data values for the Time field that falls directly on one of the hours in your criteria (6:00:00 PM for instance), that value will NOT be counted in either column in which it appears. With < or > operators, the value you specify is used as the limit and not included in the dataset. Conversely, if you were to try to have that value included in your dataset, make sure it's not going to be counted in more than one column. So your criteria should probably be:

time3- time>6:00:00 AM and time<9:00:01 AM
time4- time>9:00:00 AM and time<4:00:01 PM