One Form of Statistical Analysis

  • Thread starter Shawn Bradley via AccessMonster.com
  • Start date
S

Shawn Bradley via AccessMonster.com

I created a database that allows users to fill in information about safety
incidents. I work for a hospital so the information they have to choose
from is "Who was involved?" (Patient, Family Member, etc.), "What kind of
Incident was it? (Fall, Burn, Delay in Care, Delay in Medication Delicery,
etc.), "What is the level of Severity?" (1-6 with explainations).
It all works great, not I need to report some of the data. I need info such
as counts per quarter: How many Patients fell with a severity level of 2,
How many patients have a delay in medication delivery with a level 4
severity.
I would love to have one form that shows all of this "End of Quarter" type
information so that multiple queries do not have to be made or ran
everytime. I am currently exporting this to Excel (which I'm much better at)
to get some of this info.
Any help would be great.
Shawn
 
K

Klatuu

If the information you need is in a table, no problem; however, it looks like
you are wanting to gather info from one or more tables, summarize the data,
and present it on one form. If my assumption is correct, then one or more
queries will be required. You don't have to run the primary query in advance
of opening the form, just make it the data source for the form. It will take
a little work on the front end, but once done, it will be a lot faster than
exporting to Excel and playing with it.
 
R

Ronald W. Roberts

Shawn said:
I created a database that allows users to fill in information about safety
incidents. I work for a hospital so the information they have to choose
from is "Who was involved?" (Patient, Family Member, etc.), "What kind of
Incident was it? (Fall, Burn, Delay in Care, Delay in Medication Delicery,
etc.), "What is the level of Severity?" (1-6 with explainations).
It all works great, not I need to report some of the data. I need info such
as counts per quarter: How many Patients fell with a severity level of 2,
How many patients have a delay in medication delivery with a level 4
severity.
I would love to have one form that shows all of this "End of Quarter" type
information so that multiple queries do not have to be made or ran
everytime. I am currently exporting this to Excel (which I'm much better at)
to get some of this info.
Any help would be great.
Shawn
I have a Demographic table that looks like this:

RecID Integer Just a sequence number from 1 to what ever
TableID Text 50 Name of the table I want to do calculations on
FieldID Text 50 Field Name in the table
TypeID Text 1 1 is a count, 2 is a sum
Instr1 Text 255 Part 1 of the SQL statement I need to do the
calculation
Instr2 Text 255 Part 2 of the SQL statement I need to do the
calculation
Descrip Text 100 Description I use for display on a form or report
Result Single The resule of the calculation. A count or a
Sum.
Percent Single What percent this is of all the records for
a count calculation.
This is not used on a sum calculation.

The data looks like this:
Recid, TableID FieldID, Type, Instr1, Instr2, Descrip, Result, Percent
1, Student, Su_StateCode, 1, U_StateCode=SU_StateCode, ,Total Students,
123.00,100.00
2, Student, SU_StateCode, 1, Ucase(Trim(SU_Gender))="FEMALE", ,Females,
75, 60.90

3, Student, SU_AttndHrs, 2, SU_entrylevel=1 AND SU_enrolled=true, ,Total
Hours Entry level 1 Enrolled,
40.00, 0.00

Record 1 will count all of the records where SU_StateCode is equal to
SU_StateCode, so the count will
be a total student count or 100 percent. total count 123 and percent is
100.00

Record 2 will count the SU_StateCode for all female students where
SU_Gender is equal to Female.
total count is 75 females and 60.90 percent.

Record 3 will sum the SU_AttndHrs where entry level is 1 and enrolled is
true. The result will be a total
attended hours for all students who are in level 1. There is not
percent calculated for this record.

Note: If my where statement, or Instr1 and Instr2, goes over 255, I put
the second part in Instr2 and
do a concatation to make them 1 string or where clause.

My form has all of the controls in the header of the form. The detail
section is one subform in
Datasheet view. The subform' s record source is the demographic table.
I only show the user
the record id, description, result, and the percent. The user clicks an
update button and the program
will update all of the results and percents in the demographic table. I
also have a progress bar that
shows what record it is on and percent completed, and a print preview
buttom. When the update is
completed, they can view the results in the subform or print them out.

I did it this way because every week the user wanted more or different
results from the database.
At one time there were over 900 different queries that counted or summed
something in the database.
Today there are 583. When a user would ask "I wonder how many whatever
there are in the database",
all I had to do was add 1 record to the demographic table to get them
the answer. The demographic table
didn't just process the student table, there are 5 different tables
which I do calculations on.

I use DAO to update the results and process the demographic table. At
the time I wrote this I used
DCount and DSum to calculate the results.


Hope This Helps,

Ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top