Link to home
Start Free TrialLog in
Avatar of Imoutwest
Imoutwest

asked on

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.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

A sample database, with any confidential data purged or obfuscated, would be helpful :)
Avatar of Imoutwest
Imoutwest

ASKER

Thank you for your suggestion. I've added a database with the two tables.

 - imoutwest
ExampleData.mdb
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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
Thanks for the assistance.