Report Coding

[Chris]

I have a report with 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]
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) Between DateSerial(Year(Date()),1,1) And
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)="US") 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");

Here is my problem. I work for a hotel company and when we create a report
for a hotel that tells them they need to renovate/replace certain areas of
the hotel (marked in Tbl_PIP.Need). Some but not all of the products
(lights, carpet) we tell them to replace have to be approved by us and are
indicated as requiring "specs" (Tbl_PIP.[Requires Specs]. To further
complicate matters, not all of the Tbl_PIP.Need items have to be completed at
the same time so we give the hotel phases to do the work. Consequently, some
of the items marked as requiring specs get phased out.

Now I'm running the report above but the problem is that it is pulling all
of the items that are marked as Tbl_PIP[Requires Specs] for each hotel and
counting them and I only want it to do that if the phase due date
(Tbl_PIP_Phases.Date) is equal to or less than todays date.

Example: Hotel ABCD has to replace the following items:
1) Lights
2) Carpet
3) Parking Lot
4) Sinks
5) Wallpaper

Of the 5 items, only items 1,2 & 5 require specs. Items 1 & 2 are due on
10/1/07 (phase 1) and item 5 is due on 1/1/08.

I want the report to only display items 1 & 2 as they have been approved
(Tbl_PIP.[Spec Approval Date] and the phase date is past. The way it is
currently written, all 3 items are being displayed. Any help would be
appreciated. If you need additional clarification, please let me know.