| Author |
Query problem in Access
|
|
| DataRN 2002-10-18, 2:47 pm |
| I need help with a query in Access
I have 2 tables
1- Patients
Fld1- PtID- PrimKey
Fld2- PtName
Fld3- ClinicName
2 Test Done
Fld1- PtID- PrimKey
Fld2- DateTestDone
I am trying to calculate the percentage of patients with the test complete for each clinic. I have set up 2 queries to add a CountOf field for each table but I get 500 blanks and 100% for each clinic.
Any suggestions?
Thanks,
Bob | |
| RayMilhon 2002-10-28, 7:14 pm |
| If your still looking for an answer to this I would create 1 query
Put both tables and Link them with an outer join all of Patients and matching testdone. The Select fields would be fld3 and expr:Count(b.ptid)/Count(*)
The SQL would look like this:
Select Patients.fld3, Count(testdone.ptid)/Count(patients.*) As Percentdone
from Patients Left Outer Join testdone on
Patients.ptid = testdone.ptid
Group by Patients.fld3
The Left Outer join will produce nulls in the testdone.ptid where the test is not done and the Count(testdone.ptid) function will only count ptid's that are not null.
the count(*) will count all records Grouping by the clinicname will give the testsdone/allpatients for that clinic. | |
| DataRN 2002-11-01, 1:18 pm |
| Thanks.
What I ended up doing was creating a query that did your outer join and then selected the clinic to group by and then did a count of the ID# from table 1 a count of the ID3 in table 2 and then built an expression to do the percentage as the last field.
Thanks for your help.
Bob |
|
|
|