Challenging Query

[Jess]

Hi everybody!


I have 2 tables in my database, a table with Buildings and a table with
inspections of those buildings.


One table has buildings with data related to them, and the other one has
inspection dates with inspection-related data.


The below tables resemble my actual tables. These are the rules for both
tables:

-No building can appear twice in the Building Table. However, two buildings
can have the same name.
-A can building can be inspected multiple times or none. For instance:
BuildingID=2 was inspected on 5/5/008 and on 7/5/2008 (InspectionID 4 & 5).
BuildingID 4 was never inspected.
-Many engineers have failed in the past to enter the inspection date.
BuildingID=3 has an inspection with no date (InspectionID=6). BuildingID=1
has one too.

I would like to create a query that retrieves all the Buildings that have
been inspected after or on a given date, with the last inspection date for
that building, building height, and inspection type & deficiencies reported
in the last inspection.

Buildings never inspected or with a null value in the inspection field
should appear in the query –I have to be reminded that they need to be
inspected.

The below query features all the buildings inspected after 9/1/2008; all the
buildings that were never inspected; all the buildings with a null value in
the inspectionDate field; height of every retrieved building; and
deficiencies and inspectiontype of the last inspection, if any.


BuildingID LastInspectionDate Height Deficiencies InspectionType
1 9/2/2008 200 HH 7
3 Null 150 DE 3
4 Null 140 Null Null





BuildingTable:

BuildingID(PK) BuildingName Height
1 Red Building 200
2 White Building 100
3 Green Building 150
4 Brown Building 140


InspectionTable


InspectionID BuildingID InspectionDate DeficiencyCode InspectionType
1 1 1/1/2007 ff 6
2 1 9/2/2008 HH 7
3 1 NULL JJ 3
4 2 5/5/2008 KJ 3
5 2 7/5/2006 HG 3
6 3 NULL DE 3



Thanks in advance for your help

 

[Allen Browne]

Firstly you will need a query to identify the most recent inspection for
each building. To do this:
a) Create a query using InspectionTable.

b) Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

c) In the Field row, choose BuildingID.
In the Total row under this field, accept Group By.

d) Choose the InspectionDate field.
In the Total row under this field, choose Max.

e) Save the query with a name such as qryBuildingMaxInspectDate.

f) Create another query using InspectionTable and qryBuildingMaxInspectDate
as input 'tables.'

g) Drag InspectionTable.BuildingID onto
qryBuildingMaxInspectDate.BuildingID. Access draws a line between the 2
tables in the upper pane of query design. Double-click this line. Access
pops up a dialog with 3 options. Choose the one that says:
All records from InspectionTable, and any matches from
qryBuildingMaxInspectDate.
Technically, that's called an outer join.

h) Drag InspectionTable.InspectionDate onto
qryBuildingMaxInspectDate.MaxOfInspectionDate, and turn this into an outer
join also.

This query gives you all the information about the most reccent inspection
for each building, and includes buildings that have never been inspected.