Results 1 to 2 of 2
  1. #1
    vanfoster is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    1

    How to Query correct Information for multiple entires in table

    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

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a totals query to group by Facility ID and add LatestAssessmentDate:Max([Assessment Date]),FacilityInspections:Count([FacilityID]).
    Then in another query bring the original table and the new query and join FacilityID and Assessment Date from table to FacilityID and LatestAssessmentDate from query.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-11-2015, 06:45 PM
  2. Replies: 3
    Last Post: 05-02-2014, 09:27 AM
  3. Replies: 8
    Last Post: 10-23-2013, 04:37 PM
  4. Replies: 2
    Last Post: 01-17-2013, 01:25 AM
  5. Count responses based on entires of other table
    By VictoriaAlbert in forum Queries
    Replies: 2
    Last Post: 10-18-2011, 11:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums