How to set up a parameter query that gives me summary data

[Zorkmid]

Hi there.

I have an incidents database for a health facility. I want to create a little summary table or graph that summarizes of the type of incident, specific subtypes, and location for specific time periods, so that we can see whether there is improvement or deterioration over time, and identify trends. I have a parameter query set up that asks me the date range and the location before it runs, but it just returns all the records that fit.

How can I set up the query so that it will count, for example, the numkber of falls, and the number of falls caused by slips, and that sort of thing. I have been playing around in design view and pivotcharts etc and I haven't been able to figure it out. If someone could point me in the right direction Im sure I could figure out how to set up the more complex queries in time.

-Thanks,
Z

 

[jzwp22]

It sounds like you need what are called totals queries where you can count, sum, find the max or min etc. of a group of records. If you could supply some more details on your table structure (table names, field names & how they are related) & what fields you want to group by, we can help you construct the totals query.

In a general sense the query would look something like this

SELECT incidenttype, Count(incidenttype)
FROM yourquerythatyoumentioned
GROUP by incidenttype

 

[Zorkmid]

Sure, my primary key is a field called "ENCON" and there are fields called "Location", "Degree of Injury", "Type", "Specific Type" (a subset of type), "Person Affected", "Date", "Date Resolved"

Things like that. I dont think Ill need stats beyond stuff like:

-Number of records per location per month
-Number of "Specific Types" of "Type"


Things like that. Please let me know if anything else will help you help me

-Z

 

[jzwp22]

I would create the totals query based on the query you mentioned in your first post. I would go something like this (you will have to substitute the name of your actual query). This will give the total number of incidents at each location for each month.

SELECT yourqueryortablename.location,month(Incidentdate) as IncidentMonth, Count(yourqueryortablename.Location) AS CountOfIncidents
FROM yourqueryortablename
GROUP BY yourqueryortablename.location, month(Incidentdate)

As some suggestions, it is not a good idea to have spaces or special characters in your table or field names. Also, the word "date" is a reserved word in Access, so it should not be used as a field name. I changed your date field to incidentdate in the query above.

 

[Zorkmid]

This is all for access right? Kinda looks unfamiliar to the design view of queries to me.
-Z

 

[jzwp22]

Yes, it is Access, just the SQL view rather than design view of the query. SQL text is easier to show in posts. I've done a screen capture of the design view and pasted in the attached Word document. I usually do my initial construction in design view and then switch to SQL view if I want to do editting.

 

[Zorkmid]

Thanks alot! That gives me a quick run down of the total incidents per month. Any ideas on how to best drill down deeper into the data? For example : Specific Types of incidents per month, etc


Also, what is a good way to illustrate this data in table or chart form quickly?

-Z

 

[jzwp22]

You can make other queries to give you the data you want. Let's say you want to see the unique incident types that occurred at a specific location for the month of May. If you want to see detail, you would not use a totals query.

SELECT incidenttype, location
FROM yourquerythatyoumentioned
WHERE location= [Enter some location name] AND incidentdate between #5/1/2009#and #5/31/2009#

You would display data in a form; you can show that form in form view (single records) or in datasheet view (like a spreadsheet). You can also use reports to show your data in printed version. You can do charts within forms or reports as well. I believe there are wizards for that. You would base your form or report on the query you create. The best thing I can suggest is to experiment. Start off with simple queries then do some totals queries. Eventually you will need to nest queries together, but that is a little more advanced.