Query showing Inspections not performed

[Chris]

I really need some help. I have been trying to get this to work for hours
but can't solve it.

I have a list of Facilities that were inspection (tbl1). In the other table
is a listing of their inspections to include a grade and the date it
happened (tbl1)

These facilities require and inspection quarterly. We are trying to make
things easier on our inspectors on what remains to be inspected at the end
of the quarter (this way they won't miss one).

At the beginning of the quarter I know the list will list all of them. But
as they get inspected a record is created for that inspection.

The final report I would like to list all those that do not have an
inspection record for that quarter.

Any help will be appreciated from the group.

Thanks, Chris

 

[David S via AccessMonster.com]

I have a list of Facilities that were inspection (tbl1). In the other table
is a listing of their inspections to include a grade and the date it
happened (tbl1)

Um, that's a bit confusing - do you mean that tbl1 is a list of facilities
REQUIRING inspection and TBL2 is a listing of their inspections with grade
and date?

The first thing you need to do is figure out the quarter that each date
belongs to - you may have this info in a separate table (with date and
quarter), or you may be able to calculate it from the date itself.

You then need to build a query that lists all facilities for a quarter,
something like tbl1_WithQuarter:
SELECT tbl1.FacilityID, [Enter Quarter] AS Quarter
FROM tbl1;

Then, you need to use this in a Left Outer Join between the two queries, with
criteria that there are no inspections:
SELECT tbl1_WithQuarter.FacilityID, tbl1_WithQuarter.Quarter
FROM tbl1_WithQuarter LEFT JOIN tbl2_WithQuarter ON (tbl1_WithQuarter.Quarter
= tbl2_WithQuarter.InspectionQuarter) AND (tbl1_WithQuarter.FacilityID =
tbl2_WithQuarter.FacilityID)
WHERE (((tbl2_WithQuarter.FacilityID) Is Null));

 

[Chris]

Thanks, I created 2 different queries.

1) the sub query where it contained the establishments from tbl1 and all the
inspections performs (from tbl2). I also had to specify the facility was
active. Otherwise it even pulled closed facilities. I also had it prompted
me with a report form for a date range. Say start Jan 1 and end Mar 31. This
way it pulled all the inspections performed that quarter.

2) I then created a query that contained tbl1 and the sub query from #1
above. This is where the matching was performed and would give me the
results of those that have not been inspected.

I tried the report using various date ranges and it works perfectly. Shows a
record when an inspection WAS NOT performed and doesn't when it HAS BEEN
performed.

I would like to thank you very much for the answers.

Chris

I have a list of Facilities that were inspection (tbl1). In the other
table
is a listing of their inspections to include a grade and the date it
happened (tbl1)

Um, that's a bit confusing - do you mean that tbl1 is a list of facilities
REQUIRING inspection and TBL2 is a listing of their inspections with grade
and date?

The first thing you need to do is figure out the quarter that each date
belongs to - you may have this info in a separate table (with date and
quarter), or you may be able to calculate it from the date itself.

You then need to build a query that lists all facilities for a quarter,
something like tbl1_WithQuarter:
SELECT tbl1.FacilityID, [Enter Quarter] AS Quarter
FROM tbl1;

Then, you need to use this in a Left Outer Join between the two queries,
with
criteria that there are no inspections:
SELECT tbl1_WithQuarter.FacilityID, tbl1_WithQuarter.Quarter
FROM tbl1_WithQuarter LEFT JOIN tbl2_WithQuarter ON
(tbl1_WithQuarter.Quarter
= tbl2_WithQuarter.InspectionQuarter) AND (tbl1_WithQuarter.FacilityID =
tbl2_WithQuarter.FacilityID)
WHERE (((tbl2_WithQuarter.FacilityID) Is Null));