Query with unrelated tables

[JulieS]

I'm likely missing something basic, so please bear with me. I have
two tables from an old system that have inconsistent data:

tblSchedule
LocationID
ApptDate
......Other fields

tblEquipment
LocationID
InspectionDate
EquipmentID
......Other fields

The InspectionDate in tblEquipment should be the same as the
ApptDate in tblSchedule. However, they don't always match. What I
am attempting to do is show those discrepancies.

I've created an outer join to show me where a LocationID exists in
tblSchedule but does not exist in tblEquipment without trouble.

What I'd like to create is a query that shows where the LocationIDs
in the two tables match but the ApptDate and InspectionDate don't
match.

Thank you for any assistance.

Julie

 

[John Spencer]

This query will show you records in tblSchedule that do not match one or
more records in tblEquipment

SELECT tblSchedule.LocationID, tblSchedule.ApptDate
FROM tblSchedule LEFT JOIN tblEquipment
ON tblSchedule.LocationID = tblEquipment.LocationID
tblSchedule.ApptDate = tblEquipment.InspectionDate
WHERE tblEquipment.LocationId is Null

This one shows unmatched tblEquipment:

SELECT tblEquipment.LocationId, tblEquipment
FROM tblEquipment LEFT JOIN tblSchedule
ON tblEquipment.LocationId = tblSchedule.LocationID
AND tblEquipment.InspectionDate = tblSchedule.ApptDate
WHERE tblSchedule.LocationId is null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JulieS]

Thanks very much for the quick reply John. You've given me enough
information to modify your queries a bit to end up with:

SELECT tblSchedule.LocationID, tblSchedule.ApptDate
FROM tblSchedule
LEFT Join tblEquipment
ON tblSchedule.LocationID = tblEquipment.LocationID
WHERE (((tblSchedule.ApptDate <> tblEquipment.InspectionDate))

I appreciate the help.
Julie

 

[John Spencer]

That should work as long as you don't have two records for the same
location. Then you would end up getting records you don't want.

For instance
Location A has ApptDate of 1/1/2008 and 1/2/2008
And Location A has Inspectiondate 1/1/2008 and 1/2/2008.

Your query would correctly return
A 1/1/2008
and
A 1/2/2008
even though that would not be the desired result.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JulieS]

I see your point John and thanks very much. The two outer join
queries you listed show me where a LocationID doesn't exist in
either table. Is there a method to show where a LocationID matches
but the dates are different? In other words

tblSchedule:
Location A 12/15/08
Location B 12/15/08
Location A 12/10/08

tblEquipment
Location A 12/15/08
Location B 12/15/08
Location A 12/05/08

The query would return Location A 12/05/08 because the combination
of the Location plus date is not matched? I've tried combining the
two fields in the tables through a query and then creating a query
to compare but that's made an awful hash.

Thanks for your help and patience!
Julie

 

[John Spencer]

The outer join queries as posted should do what you are asking. Notice that
they test both the locations and the dates. If both location and date don't
match then the table on the right (since I used a left join) will return NULL
for EVERY field. SO you could test either (or both) of the fields of the
right-hand table involved in the join.

If you were just trying to screen on location, you would have to drop the date
match from the join clause.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[JulieS]

Thanks very much John. I appreciate your help.
Julie

 

[knightconsulting]

try firefly. it's good at showing discrepancies between two sql
resultsets. I had a similar problem which is why i wrote it.

http://www.getfirefly.net/