Query Help Needed

[BeerMan]

Ok i have a small database just about set up. Below are the 2 table that I'm using for the query.

System Details
SRN (ID number)
Field
Field
Risk_Category
Field

Inspection Details
SRN (ID number)
Inspection Date
Field
Field
Field
Field

Ok now what i'm trying to achieve:
I want to select all systems that have a Risk_Category=High that had an inspection > than 2 years ago and systems that have a Risk_Category=Medium that had an inspection > than 4 years ago.
Here is what i have so far, i got the first part to work (Risk_Category with inspection > than 2 years ago) but can't get the second part to work. I think i'm missing somthing simple:

SELECT *
FROM [System Details]
WHERE (((Exists (select [inspection details].srn
From [inspection details]
where [system details].srn = [inspection details].srn
group by [inspection details].srn
having max([inspection date] ) < (date() – 730)))<>False) AND (([System Details].Risk_Category)="High"));


Any help would be appricated.

 

[khawar]

Make two queries first to get the latest inspection dates for each item as below and save it as LastInspectionDates

SELECT [Inspection Details].SRN, 
Max([Inspection Details].InspectionDate) AS LastInspectionDate
FROM [Inspection Details]
GROUP BY [Inspection Details].SRN;

in the second query paste the following code

SELECT [System Details].Srn, 
[System Details].Risk_Category, 
LastInspectionDates.LastInspectionDate
FROM [System Details] 
INNER JOIN LastInspectionDates ON [System Details].Srn = LastInspectionDates.SRN
WHERE ((([System Details].Risk_Category)="High") AND ((LastInspectionDates.LastInspectionDate)<DateAdd("yyyy",-2,Date()))) OR ((([System Details].Risk_Category)="Medium") AND ((LastInspectionDates.LastInspectionDate)<DateAdd("yyyy",-4,Date())));

This query will show you over due inspections