Perform subquery in different order 1

[jimdltn]

How would I rearrange or rewrite this query to perform the AdminSurvey filter before it grabs the Max Date between dates? As written, it Selects the Max Dates in the date range and then filters out the AdminSurvey and I loose data. What I want is to find all records that are not AdminSurveys and then get the Max date between dates for each FacilityData.


SELECT CC_HazardLevel.Name, Main_FacilityType.Name, Main_Facility.Name, Main_Facility.Customer, Main_Facility.PrimaryAddress_Number, Main_Facility.PrimaryAddress_Street1, CC_Survey.FacilityData, CC_Survey.ID, CC_Survey.SurveyDate, CC_Survey.SurveyCompleted, CC_Survey.InCompliance, CC_Survey.PerformedBy, CC_Survey.EventId, CC_Survey.AdminSurvey, CC_Survey.childsub
FROM ((CC_FacilityData INNER JOIN CC_HazardLevel ON CC_FacilityData.CCHazardLevel = CC_HazardLevel.ID) INNER JOIN (Main_Facility INNER JOIN Main_FacilityType ON Main_Facility.Type = Main_FacilityType.ID) ON CC_FacilityData.Facility = Main_Facility.ID) INNER JOIN CC_Survey ON CC_FacilityData.ID = CC_Survey.FacilityData
WHERE (((Main_Facility.Customer)=1) AND ((CC_Survey.SurveyDate) In (SELECT Max(Tmp.SurveyDate) FROM CC_Survey As Tmp WHERE Tmp.FacilityData = CC_Survey.FacilityData AND Tmp.SurveyDate Between #2/15/2006# And #4/2/2006#)) AND ((CC_Survey.SurveyCompleted) Is Not Null) AND ((CC_Survey.AdminSurvey)=False Or (CC_Survey.AdminSurvey) Is Null))
ORDER BY Main_Facility.Name;

 

[PHV]

Typed, untested:
SELECT H.Name, T.Name, F.Name, F.Customer, F.PrimaryAddress_Number, F.PrimaryAddress_Street1, S.FacilityData, S.ID, S.SurveyDate, S.SurveyCompleted, S.InCompliance, S.PerformedBy, S.EventId, S.AdminSurvey, S.childsub
FROM ((CC_FacilityData D
INNER JOIN CC_HazardLevel H ON D.CCHazardLevel = H.ID)
INNER JOIN (Main_Facility F INNER JOIN Main_FacilityType T ON F.Type = T.ID) ON D.Facility = F.ID)
INNER JOIN CC_Survey S ON D.ID = S.FacilityData
WHERE F.Customer=1 AND S.SurveyDate In (
SELECT Max(SurveyDate) FROM CC_Survey WHERE FacilityData = S.FacilityData AND SurveyDate Between #2/15/2006# And #4/2/2006#
AND SurveyCompleted Is Not Null AND Nz(AdminSurvey,False) = False
) AND S.SurveyCompleted Is Not Null AND (S.AdminSurvey=False Or S.AdminSurvey Is Null)
ORDER BY F.Name

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

 

[jimdltn]

Thanks PHV for the effort but it returned nothing. I extrapolated from your offering as:

SELECT CC_HazardLevel.Name, Main_FacilityType.Name, Main_Facility.Name, Main_Facility.Customer, Main_Facility.PrimaryAddress_Number, Main_Facility.PrimaryAddress_Street1, CC_Survey.FacilityData, CC_Survey.ID, CC_Survey.SurveyDate, CC_Survey.SurveyCompleted, CC_Survey.InCompliance, CC_Survey.PerformedBy, CC_Survey.EventId, CC_Survey.AdminSurvey, CC_Survey.childsub
FROM ((CC_FacilityData INNER JOIN CC_HazardLevel ON CC_FacilityData.CCHazardLevel = CC_HazardLevel.ID) INNER JOIN CC_Survey ON CC_FacilityData.ID = CC_Survey.FacilityData) INNER JOIN (Main_Facility INNER JOIN Main_FacilityType ON Main_Facility.Type = Main_FacilityType.ID) ON CC_FacilityData.Facility = Main_Facility.ID
WHERE (((Main_Facility.Customer)=1) AND ((CC_Survey.SurveyDate) In (SELECT Max(SurveyDate) FROM CC_Survey WHERE FacilityData = CC_Survey.FacilityData AND SurveyDate Between #2/15/2006# And #4/2/2006#) And "SurveyCompleted" Is Not Null And Nz("AdminSurvey",False)=False) AND ((CC_Survey.SurveyCompleted) Is Not Null) AND ((CC_Survey.AdminSurvey)=False Or (CC_Survey.AdminSurvey) Is Null))
ORDER BY Main_Facility.Name;

Did I miss something?

 

[PHV]

Did I miss something?
You have to use alias in the subquery as you use 2 instances of the CC_Survey table.

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

 

[PHV]

Another way:
SELECT H.Name, T.Name, F.Name, F.Customer, F.PrimaryAddress_Number, F.PrimaryAddress_Street1, S.FacilityData, S.ID, S.SurveyDate, S.SurveyCompleted, S.InCompliance, S.PerformedBy, S.EventId, S.AdminSurvey, S.childsub
FROM (((CC_FacilityData D
INNER JOIN CC_HazardLevel H ON D.CCHazardLevel = H.ID)
INNER JOIN (Main_Facility F INNER JOIN Main_FacilityType T ON F.Type = T.ID) ON D.Facility = F.ID)
INNER JOIN CC_Survey S ON D.ID = S.FacilityData)
INNER JOIN (
SELECT FacilityData, Max(SurveyDate) AS LastDate FROM CC_Survey WHERE SurveyDate Between #2006-02-15# And #2006-04-02#
AND SurveyCompleted Is Not Null AND Nz(AdminSurvey,False) = False GROUP BY FacilityData
) M ON S.FacilityData = M.FacilityData AND S.SurveyDate = M.LastDate
WHERE F.Customer=1 AND S.SurveyCompleted Is Not Null AND Nz(S.AdminSurvey,False) = False
ORDER BY F.Name

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

 

[jimdltn]

What does the M represent?

 

[jimdltn]

Okay, I got it, "the alias." This works to a T. Thanks very much PHV. I was beginning to tweak.