I have an Access database of 406 records. The records actually consist of 406 patients, what physician they were treated by, the number of visits they received, the number of labs they received, whether or not they received an eye exam and/or pap smear, and a comments field (for extra general information). Each patient will only have one physician.

I want to calculate a compliance rate for the number of visits received, number of labs received, eye exams received, and paps received. First, I want to have an overall compliance rate for each of the indicators listed. Then, I want to calculate an overall compliance rate for each of the indicators listed by the individual physician. I have approximately 33 possible physicians.

Is there an easy way for me to get these calculations without running a query for each individual indicator/physician?

Tonya

I only have one table in which all of the data is stored. It was built this way so that data entry would be easier. This is information that was collected to monitor quality of care issues.

hotdiamond, can you post the database with a small amount of "dummy data" so that we can look at it and possibly give some more help?

Assuming you have a table with the following fields:
Provider_ID as Number
Patient_ID as Number
Visit_Count as Number
Lab_Count as Number
Eye_Exam_Count as Number
Pap_Smear_Count as Number

You could create a union Query as follows:
(Note you can only do union queries in SQL - although you can create each part of the union query in a design window and then copy the SQL to a union query)

--- SQL Example Below -----

SELECT tblPatient.ProviderID, tblPatient.Patient_ID, tblPatient.Visit_Count AS [Counter], "Visit" AS Type
FROM tblPatient

Union

SELECT tblPatient.ProviderID, tblPatient.Patient_ID, tblPatient.Lab_Count AS [Counter], "Lab" AS Type
FROM tblPatient

Union

SELECT tblPatient.ProviderID, tblPatient.Patient_ID, tblPatient.Eye_Exam_Count AS [Counter], "Eye" AS Type
FROM tblPatient

Union

SELECT tblPatient.ProviderID, tblPatient.Patient_ID, tblPatient.Pap_Smear_Count AS [Counter], "Pap" AS Type
FROM tblPatient;

----- Source Data --------
Patient_ID ProviderID Visit_Count Lab_Count Eye_Exam_Count Pap_Smear_Count
1 1 23 2 1 0
2 1 5 1 0 0
3 2 15 0 1 0
4 2 2 0 0 1

----- Resulting Data ----------
ProviderID Patient_ID Counter Type
1 1 0 Pap
1 1 1 Eye
1 1 2 Lab
1 1 23 Visit
1 2 0 Eye
1 2 0 Pap
1 2 1 Lab
1 2 5 Visit
2 3 0 Lab
2 3 0 Pap
2 3 1 Eye
2 3 15 Visit
2 4 0 Eye
2 4 0 Lab
2 4 1 Pap
2 4 2 Visit

Well. You might be able to use a crosstab query of labs per patient grouped by physician if you use some self-joins. I should say, any SQL solution is going to involve self-joins if you want to do it all in one shot. Otherwise you'll just have to build a query for each physician. That's why normalization is so important when you start designing a database. A lot of people say the requirement doesn't necessitate a normalized design, but it's always worth the extra effort up front.

Sorry.

chris.

It all depends on your table structure (like just about anything in a relational database); this will be fairly easy if you've established your relationships correctly. If you have a table for each patient, a table for patients per doctor, a table of visits per patient, and a table of labs per visit, you could build a report that presents patient statistics grouped by physician.

If, on the other hand, your database is not normalized, this will probably be very difficult.

It's hard to say more without specific information.

chris.

I usually use an IIF statement in the query. If you look at the previous query, I'll change the lab line

SELECT tblPatient.ProviderID, tblPatient.Patient_ID, IIf([tblPatient].[Lab_YN],1,0) AS [Counter], "Lab" AS Type
FROM tblPatient;

This query creates a source for your crosstab query or reports. You can sum, avg, std, or whatever you like to it after you've created the Union Query.

Thanks for the great advice everyone. I actually ran a crosstab query to group the providers, and then I created a report from the crosstab query to show the compliance rates. Thanks again!

Thanks for the replies everyone. I'm in a hurry right now and can't really answer any of the questions. I'll reply back later. But just as a quick note, all of the fields I need to calculate are not numbers. 2 of the fields are number fields and 2 are yes/no fields. Will this make it more difficult to calculate? I really need to use the count function first and then find out the percentage. Is this possible? I would think that there would be some way to count all yes's, no's, get a number and then calculate the percentage. Please let me know if this is not possible and I'll try to think of another way.

Thanks again guys.