Find Unmatched Query

G

Guest

I have an unmatched query (Using the wizard) that is returning only a portion
of the results I need because there are two primary keys in the related
tables. I need to add a something to the SQL statement to account for the
Join on the second primary key but can't quite get it - any tips? SQL
statement is below I need to add a join on the field Formation.

SELECT Picks.Loc_ID, Picks.Top_Elev, Picks.Formation, Picks.SessionNum,
Picks.Deleted, Picks.Duplicated, Picks.PickDate, Picks.UniqueID,
Picks.xlocation04, Picks.ylocation04, Picks.elev04, Picks.Loc_Name04,
Picks.Loc_Name_Alt1_04, Picks.xlocation06, Picks.ylocation06, Picks.elev06,
Picks.Loc_Name06, Picks.Loc_Name_Alt1_06, Picks.GndElevChange
FROM Picks LEFT JOIN [RG Picks] ON Picks.Loc_ID = [RG Picks].Loc_ID
WHERE ((([RG Picks].Loc_ID) Is Null));
 
K

knightconsulting

when comparing data from two tables/queries the find unmatched query
in access is inadequate. i wrote Firefly because i needed to know what
VALUES don't match across all fields for each record that wasn't
identical. Same thing with oracle and the MINUS query - sure you see
5,000 records that don't match but why don't they match? In many cases
more than one field may not match and it's not enough to just see what
records don't match - you need to see exactly why they don't match,
field by field. http://www.getfirefly.net/
 
J

John Spencer

I assume that you mean that you have 2 fields that combined are the primary
key. If that is so you should be able to do the following

SELECT Picks.Loc_ID, Picks.Top_Elev, Picks.Formation, Picks.SessionNum,
Picks.Deleted, Picks.Duplicated, Picks.PickDate, Picks.UniqueID,
Picks.xlocation04, Picks.ylocation04, Picks.elev04, Picks.Loc_Name04,
Picks.Loc_Name_Alt1_04, Picks.xlocation06, Picks.ylocation06, Picks.elev06,
Picks.Loc_Name06, Picks.Loc_Name_Alt1_06, Picks.GndElevChange
FROM Picks LEFT JOIN [RG Picks]

ON Picks.Loc_ID = [RG Picks].Loc_ID
AND Picks.Formation = [RG Picks].Formation

WHERE ((([RG Picks].Loc_ID) Is Null));

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