Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2015
    Posts
    7

    Unanswered: Query formula based on last inspection results

    I have a list of inspection data collected and logged by date. I'm looking for a formula that will return results based on only the results of the last inspection date. For example using the data below, I'd like for equipment number 2 to be in the query results because the last inspection results are "not in service". Where equipment number 1 would not be returned in the query results because the last inspection results were not "Not in Service".

    The fields I have in the query are:

    Equipment Test Results Inspection date
    1 Not in Service 10/10/2011
    1 In Service 12/1/2012
    1 Not in Service 2/22/2013
    1 In Service 11/24/2014

    2 In Service 10/10/2011
    2 In Service 12/1/2012
    2 In Service 2/22/2013
    2 Not in Service 11/24/2014

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dwright2 View Post
    I have a list of inspection data collected and logged by date. I'm looking for a formula that will return results based on only the results of the last inspection date. For example using the data below, I'd like for equipment number 2 to be in the query results because the last inspection results are "not in service". Where equipment number 1 would not be returned in the query results because the last inspection results were not "Not in Service".
    So you actually need to use two criteria:
    1) Inspection_date = Max(Inspection_date)
    AND
    2) Test_Results ="Not in Service"

    You can use a subquery in a WHERE clause, but Access does not always allow it:
    Code:
    SELECT Equipment, Test_Results, Inspection_date
    FROM Tbl_Inspection_Data 
    WHERE Test_Results = 'Not in Service' AND
          Inspection_date = (SELECT MAX(Inspection_date) FROM Tbl_Inspection_Data);
    However, nothing prevents you from using a subquery in an INNER JOIN.

    Two cases are possible:
    1) The query can return no (zero) row if Max(Inspection_date) is found in a row where Test_Results ="In Service":
    Code:
    SELECT Equipment, Test_Results, Inspection_date
    FROM Tbl_Inspection_Data 
    INNER JOIN (SELECT MAX(Inspection_date) AS MaxDate FROM Tbl_Inspection_Data) AS A  ON A.MaxDate = Tbl_Inspection_Data.Inspection_date
    WHERE Test_Results = 'Not in Service' ;
    2) The query always returns the last row where Inspection_date = Max(Inspection_date) and Test_Results ="Not in Service":
    Code:
    SELECT Equipment, Test_Results, Inspection_date
    FROM Tbl_Inspection_Data 
    INNER JOIN (SELECT MAX(Inspection_date) AS MaxDate FROM Tbl_Inspection_Data WHERE Test_Results = 'Not in Service') AS A  ON A.MaxDate = Tbl_Inspection_Data.Inspection_date
    WHERE Test_Results = 'Not in Service';
    Have a nice day!

  3. #3
    Join Date
    Aug 2015
    Posts
    7
    Thanks for the quick reply!

    Your code establishes the MaxDate as the latest date in the table. Is there a way to establish the MaxDate by individual piece of equipment. I envision the loop logic going something like:

    1. Has the piece of equipment ever had an inspection result of "Not in Service".
    2. No, don't show it in the results.

    3. Has the piece of equipment ever had an inspection result of "Not in Service".
    4. If Yes, has it had an "In Service" inspection since?
    5. If Yes, then don't show it in the results.

    6. Has the piece of equipment ever had an inspection result of "Not in Service".
    7. If yes, has it had an "In Service" inspection since?
    8. If no, then what was the date of this "Not in Service" inspection?
    Last edited by dwright2; 11-10-15 at 19:59. Reason: Grammatical corrections

  4. #4
    Join Date
    Aug 2015
    Posts
    7
    Additional research (trial and error) suggests a sub-query is the way to go.....I suspect it could be done in VBA with a loop function also.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •