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

Posted on 2011-10-28
Last Modified: 2012-05-12
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.
Question by:Imoutwest
    LVL 92

    Expert Comment

    by:Patrick Matthews
    A sample database, with any confidential data purged or obfuscated, would be helpful :)
    LVL 7

    Author Comment

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

     - imoutwest
    LVL 92

    Accepted Solution

    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
    GROUP BY z.Mon
    ORDER BY z.Mon

    Open in new window

    LVL 7

    Author Comment

    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?
    LVL 7

    Author Comment

    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
    LVL 7

    Author Closing Comment

    Thanks for the assistance.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now