Data Selection Problem

[Chris]

I am trying to print a report that has the following SQL.

SELECT Tbl_Hotels.ID, Tbl_Hotels.[Prop Code], Tbl_Hotels.[Property Name],
([CountofSpec Approval Date]/[CountofRequires Specs]) AS [Percent Approved],
IIf(Tbl_Hotels![Contractual Reno]=True,"Contractual Reno","Non-Contractual
Reno") AS [Reno Type], Count(Tbl_PIP.[Requires Specs]) AS [CountOfRequires
Specs], Count(Tbl_PIP.[Spec Approval Date]) AS [CountOfSpec Approval Date],
Tbl_Inspect.Date
FROM Tbl_PIP INNER JOIN (Tbl_Hotels INNER JOIN (Tbl_Inspect INNER JOIN
Tbl_PIP_Phases ON Tbl_Inspect.InspectionID = Tbl_PIP_Phases.InspectionID) ON
Tbl_Hotels.ID = Tbl_Inspect.[Hotel ID]) ON (Tbl_PIP.InspectionID =
Tbl_Inspect.InspectionID) AND (Tbl_PIP.InspectionID =
Tbl_PIP_Phases.InspectionID)
WHERE (((Tbl_PIP_Phases.Date)<Date()) AND ((Tbl_PIP.[Requires Specs])=True)
AND ((Tbl_Inspect.Status) In ("Reno","NC-Reno")) AND
((Tbl_Hotels.Country)="US")) OR (((Tbl_PIP.[Requires Specs])=True) AND
((Tbl_Inspect.Status) In ("Reno","NC-Reno")) AND ((Tbl_Hotels.Country)="CA")
AND ((Tbl_PIP_Phases.[Prior to Opening])=True))
GROUP BY Tbl_Hotels.ID, Tbl_Hotels.[Prop Code], Tbl_Hotels.[Property Name],
IIf(Tbl_Hotels![Contractual Reno]=True,"Contractual Reno","Non-Contractual
Reno"), Tbl_Inspect.Date
HAVING (((Tbl_Inspect.Date)>#6/1/2006#));


I only want [CountOfRequires Specs] to count those items that are in one of
the phases where (Tbl_PIP_Phases.Date) < Date().

I can't figure out how to write this. Any help would be appreciated.