Hi all,
First post here so I hope im providing the correct and enough information for everyone.
I'm having real trouble with a query which I can't get to work properly.
I have 2 tables, one table with items and another with test dates which FK to the PK of SerialNo of the items.
When I run the query I get the following results:
SerialNo Description InspectedDate NextInspectionDate SafeForUse Missing 002106678 Round Slings Polyester1M EWL 16/01/2018 16/07/2018 Yes No 002106678 Round Slings Polyester1M EWL 16/01/2018 16/07/2018 Yes No 002106678 Round Slings Polyester1M EWL 17/01/2018 17/01/2018 Not Inspected Yes 002106678 Round Slings Polyester1M EWL 17/07/2018 17/01/2019 Yes No 002106678 Round Slings Polyester1M EWL 18/07/2018 18/07/2018 Not Inspected Yes 002106678 Round Slings Polyester1M EWL 15/01/2019 15/07/2019 Yes No 002106678 Round Slings Polyester1M EWL 16/01/2019 16/07/2019 Yes No 002106678 Round Slings Polyester1M EWL 17/01/2018 17/01/2018 Not Inspected Yes 002106678 Round Slings Polyester1M EWL 17/07/2018 17/01/2019 Yes No 002106678 Round Slings Polyester1M EWL 18/07/2018 18/07/2018 Not Inspected Yes 002106678 Round Slings Polyester1M EWL 15/01/2019 15/07/2019 Yes No 002106678 Round Slings Polyester1M EWL 16/01/2019 16/07/2019 Yes No
I understand I can use MAX for the Inspected and Next Inspected dates with totals which would give me the following:
SerialNo Description InspectedDate NextInspectionDate SafeForUse Missing 002106678 Round Slings Polyester1M EWL 18/07/2018 18/07/2018 Not Inspected Yes 002106678 Round Slings Polyester1M EWL 16/01/2019 16/07/2019 Yes No
What I would like the query to do however is only display the SafeForUse record which is Yes. I can't however set criteria to "Yes" in that column however as there may be some items that are missing and not safe for use for within the test period.
Because the query is coming out like that, when we pull reports for missing items for their test period they are coming out as missing when actually they aren't as there is a SafeForUse record within date so the Not Inspected one should therefore not show in the query.
I hope this makes sense. I'm just not sure how to proceed with this query.
Hope someone can help.
Many thanks,
Chris