• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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
  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.
  • 4
  • 2
1 Solution
Patrick MatthewsCommented:
A sample database, with any confidential data purged or obfuscated, would be helpful :)
ImoutwestAuthor Commented:
Thank you for your suggestion. I've added a database with the two tables.

 - imoutwest
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
    (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

Open in new window

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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?
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
ImoutwestAuthor Commented:
Thanks for the assistance.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now