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
