How to show if last inspection date is > 1 year! 1

[Makita92]

Hi Everyone,

I am going to try and explain what I want and I hope that it makes sense.

First I have a database that contains multiple tables (Listed below is only the fields needed).

Harbour Table
Harbour Code
Harbour Name

Facility Table
Facility Code
Harbour Code

Inspection Table
Inspection Number
Facility Code
Date of Inspection

When I join the tables together in a query the data will look something like this. (Harbour Code, Harbour Name, Facility Code, Inspection Number and Date of Inspection)

1 Bay Bulls 990 200 2005-11-30
1 Bay Bulls 990 201 2004-11-30
4 Bonavista 991 202 2004-11-29

Now I would like the query to list all the harbours and facility codes that have not received inspections within the last year. When I run the query the Bay Bulls Inspection 201 record (along with the Bonavista record)appears but due to an inspection being performed on 2005-11-30 to the same facility code (990) only the Bonavista record should be listed.

Can someone please help me?

Thank you in advance!

 

[lespaul]

When I run the query

could you post the SQL of the query?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Makita92]

SELECT tblInshoreFishHandling.intHarbourCode, tblInshoreFishHandling.txtLocalName, tblInshoreFishHandling.chkOutOfService, tblInshoreFishHandling.intFacilityCode, tblInspectionInformation.dtInspectionDate
FROM tblInshoreFishHandling INNER JOIN tblInspectionInformation ON (tblInshoreFishHandling.intFacilityCode = tblInspectionInformation.intFacilityCode) AND (tblInshoreFishHandling.intHarbourCode = tblInspectionInformation.intHarbourCode)
WHERE (((tblInshoreFishHandling.chkOutOfService)=False) AND ((tblInspectionInformation.dtInspectionDate)<#1/1/2005#))
ORDER BY tblInshoreFishHandling.intHarbourCode, tblInshoreFishHandling.intFacilityCode;

 

[PHV]

SELECT F.intHarbourCode, F.txtLocalName, F.intFacilityCode, Max(I.dtInspectionDate) As LastInspection
FROM tblInshoreFishHandling F INNER JOIN tblInspectionInformation I ON F.intFacilityCode = I.intFacilityCode AND F.intHarbourCode = I.intHarbourCode
WHERE F.chkOutOfService = False
GROUP BY F.intHarbourCode, F.txtLocalName, F.intFacilityCode
HAVING Max(I.dtInspectionDate) < Date()-365
ORDER BY F.intHarbourCode, F.intFacilityCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886