Report Percentages of Inspected Clinics by MM/YY and Separates List of Clinics not Inspected
Posted on 2011-10-28
Hello again, its been a number of years since I asked a question (back to creating database again). Hopefully I can make some sense. This should be easy, but I can't figure it out.
I have 2 tables: tblInspections and tblClinics
tblClinics is a list of sites (Clinics, currently 64) that have to be inspected monthly and is used as a source for looking up clinics in the other table.
tblInspections is a list of clinics inspections that have been completed. All 64 clinics should be inspected monthly, thus each month 64 new records should be added to this table. These inspections happen throughout the month. There are numerous text fields for Yes/No/N/A for the areas that are inspected.
I need a query/report(s):
That provides a percentage of Clinics that a been inspected out of the total number of clinics listed in tblClinics.
A list of Clinics that have not been inspected for a given Month/Year.
Sample of Data.
I-Ambulance (Medic 1, 2 & 4)
S-Army Community Health
S-Dental Clinic - MAMC
tblInspections: Date; Clinics
10/20/2011 Allergy/Immunization Clinic
10/20/2011 OB/GYN CLINIC
10/20/2011 Gastro Clinic
10/24/2011 Physical Therapy/Physical Medicine
10/26/2011 Rheumatology/Endocrine/Diabetes Clinic
10/26/2011 Dermatology Clinic
10/26/2011 Urology Clinic
10/26/2011 Internal Med Clinic
10/26/2011 Family Practice
10/26/2011 2 North
Thank you for your help, hopefully this is enough information.