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/Dia betes 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.
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/Dia
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.
A sample database, with any confidential data purged or obfuscated, would be helpful :)
ASKER
Thank you for your suggestion. I've added a database with the two tables.
- imoutwest
ExampleData.mdb
- imoutwest
ExampleData.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Could you explain why it works, I want the answers but I like to learn the why?
ASKER
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
A list of Clinics that have not been inspected for a given Month/Year?
- imoutwest
ASKER
Thanks for the assistance.