I have an issue that I can't quite think around so any insight into building the original table would be helpful.
My current database has 2 different tables that I am trying to work with.
1. Facility Name which contains the basic information for the facility (name, address, internal identifying codes) Facility ID is the key for this table and the link to all connected tables
2. Risk Assessment which contains Incident ID, Facility ID, Fiscal Year, Current Risk Level, Risk Assessment Date, Risk Successful Date, Risk Level Compromised.
The employees in my office must go out every three months to do an inspection until the facility is at a low risk level. Currently I have the table creating a new incident every time my employees go to the facility. I am having an issue when I try to query current Risk Levels.
Example of Current Data
Facility ID Incident ID Risk Level Assessment Date Successful Date Compromised
1 2 Medium 10/19/20 yes
1 3 Medium 12/1/20 yes
6 4 Low 12/10/20 12/1/20 no
2 5 Medium 1/5/21 yes
1 6 Low 1/31/21 1/31/21 no
I would like to create a report for all facilities that are currently above a low risk level. When I do a query for facilities that are not equal to low all incidents are pulled even when the facility has finally achieved the correct risk level. Based on the above table I would like to figure out a way for the query to pull only the Facility 2. I also need to have the historical data showing how many times the facility was visited for a different report so can't just change the risk level in the first incident.
Thank you