Report Percentages of Inspected Clinics by MM/YY and Separates List of Clinics not Inspected

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.

tblClinics: Clinics
  M-Airmans' Clinic
  S-Allergy/Immunization Clinic
  I-Ambulance (Medic 1, 2 & 4)
  S-Army Community Health
  O-Cardiology Clinic
  O-Cast Room
  I-Cath Lab
  M-Dental
  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/24/2011      Pediatrics
  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.
LVL 7
ImoutwestAsked:
Who is Participating?
 
Patrick MatthewsCommented:
This seems to be working.

Any single clinic only gets counted once per month, even if that single clinic gets inspected more than once in the month.


SELECT z.Mon, Count(z.Site) / First(y.Clinics) AS Perc
FROM
    (SELECT Format(i.[Date], "yyyy-mm") AS Mon, i.Site
    FROM tblInspections i
    GROUP BY Format(i.[Date], "yyyy-mm"), i.Site) AS z,
    (SELECT Count(*) AS Clinics
    FROM tblClinics) AS y
GROUP BY z.Mon
ORDER BY z.Mon

Open in new window

0
 
Patrick MatthewsCommented:
A sample database, with any confidential data purged or obfuscated, would be helpful :)
0
 
ImoutwestAuthor Commented:
Thank you for your suggestion. I've added a database with the two tables.

 - imoutwest
ExampleData.mdb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ImoutwestAuthor Commented:
It seems to be what I need, sorry I couldn't test it until today.

Could you explain why it works, I want the answers but I like to learn the why?
0
 
ImoutwestAuthor Commented:
Also, the second part of my question, can you answer this or am I susposed to ask it in a separate question?

A list of Clinics that have not been inspected for a given Month/Year?

- imoutwest
0
 
ImoutwestAuthor Commented:
Thanks for the assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.