Hi,
Currently I am using 3 queries to come up with the results I am looking for, but I would like to figure out the proper way to combine these 3 into 1. What I am doing is looking at a table of inspections and picking out the ones that meet a certain criteria. Here are some details:
My first query looks at the table of inspections (Merge all HYDINSP Desc) and gets the most recent inspection (INSP_DATE) for each item (FACILITYID):
SELECT [Merge all HYDINSP Desc].FACILITYID, Max([Merge all HYDINSP Desc].INSP_DATE) AS MaxOfINSP_DATE
FROM [Merge all HYDINSP Desc]
GROUP BY [Merge all HYDINSP Desc].FACILITYID;
My second query looks at that same table of inspections and looks for all of the inspections that meet the criteria:
SELECT [Merge all HYDINSP Desc].*
FROM [Merge all HYDINSP Desc]
WHERE ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].HYDRANT_OPERATION)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].OPERATING_NUT)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].NOZZLE_CAPS)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].FLANGES)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].WATER_IN_BARREL)="True"));
My third criteria does a join on the first two queries, since in my final results I only want to see the most recent inspection that meets the criteria. I also :
SELECT [HYDINSP All WR].*
FROM [HYDINSP Most Recent] INNER JOIN [HYDINSP All WR] ON ([HYDINSP Most Recent].MaxOfINSP_DATE = [HYDINSP All WR].INSP_DATE) AND ([HYDINSP Most Recent].FACILITYID = [HYDINSP All WR].FACILITYID)
ORDER BY [HYDINSP All WR].INSP_DATE DESC;
Can anybody help me figure out how to do this in one SQL statement?? I prefer to use the GUI interface in Access, which I guess is why I've always broken it down it separate smaller queries, but there must be a way to do this in one SQL statement.
Thanks!
Currently I am using 3 queries to come up with the results I am looking for, but I would like to figure out the proper way to combine these 3 into 1. What I am doing is looking at a table of inspections and picking out the ones that meet a certain criteria. Here are some details:
My first query looks at the table of inspections (Merge all HYDINSP Desc) and gets the most recent inspection (INSP_DATE) for each item (FACILITYID):
SELECT [Merge all HYDINSP Desc].FACILITYID, Max([Merge all HYDINSP Desc].INSP_DATE) AS MaxOfINSP_DATE
FROM [Merge all HYDINSP Desc]
GROUP BY [Merge all HYDINSP Desc].FACILITYID;
My second query looks at that same table of inspections and looks for all of the inspections that meet the criteria:
SELECT [Merge all HYDINSP Desc].*
FROM [Merge all HYDINSP Desc]
WHERE ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].HYDRANT_OPERATION)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].OPERATING_NUT)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].NOZZLE_CAPS)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].FLANGES)<>"OK")) OR ((([Merge all HYDINSP Desc].DATEWORK) Is Null) AND (([Merge all HYDINSP Desc].WATER_IN_BARREL)="True"));
My third criteria does a join on the first two queries, since in my final results I only want to see the most recent inspection that meets the criteria. I also :
SELECT [HYDINSP All WR].*
FROM [HYDINSP Most Recent] INNER JOIN [HYDINSP All WR] ON ([HYDINSP Most Recent].MaxOfINSP_DATE = [HYDINSP All WR].INSP_DATE) AND ([HYDINSP Most Recent].FACILITYID = [HYDINSP All WR].FACILITYID)
ORDER BY [HYDINSP All WR].INSP_DATE DESC;
Can anybody help me figure out how to do this in one SQL statement?? I prefer to use the GUI interface in Access, which I guess is why I've always broken it down it separate smaller queries, but there must be a way to do this in one SQL statement.
Thanks!