query to find Unmatched records
I have 2 tables in access 2003
one is named WorkingCosList and the other is named
ROW_Id_Adp_Id_Cross_Link both with same fields
Here is a sample of data from WorkingCosList
filenum shop emp name
1 1201 john doe
2 1201 jane doe
3 1201 jim doe
Here is a sample of data from ROW_Id_Adp_Id_Cross_Link
filenum shop emp name
1 1201 john doe
2 1201 jane doe
I want a query that will return recods that are in WorkingCosList
but not in ROW_Id_Adp_Id_Cross_Link
In the example above I would want
3 1201 jim doe
I tried playing around with the unmatched query wizard but can't seem to come up with the correct query
one is named WorkingCosList and the other is named
ROW_Id_Adp_Id_Cross_Link both with same fields
Here is a sample of data from WorkingCosList
filenum shop emp name
1 1201 john doe
2 1201 jane doe
3 1201 jim doe
Here is a sample of data from ROW_Id_Adp_Id_Cross_Link
filenum shop emp name
1 1201 john doe
2 1201 jane doe
I want a query that will return recods that are in WorkingCosList
but not in ROW_Id_Adp_Id_Cross_Link
In the example above I would want
3 1201 jim doe
I tried playing around with the unmatched query wizard but can't seem to come up with the correct query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The unmatched query wizard does exactly what you are asking for.
Sql - wise...
Select * from WorkingCosList as A left join ROW_Id_Adp_Id_Cross_Link as B
on A.Filenum = B.filenum
where B.Filenum is null
Sql - wise...
Select * from WorkingCosList as A left join ROW_Id_Adp_Id_Cross_Link as B
on A.Filenum = B.filenum
where B.Filenum is null
The question here is whether FileNum is the ID corresponding to the person's name (Jane Doe, etc.). If so, the above suggestions will work. If not, then you need to check for a match on the Emp Name field.
WHERE IsNull(b.filenum);