Solved

query to find Unmatched records

Posted on 2011-02-15
4
378 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:johnnyg123
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34898420
select * from
WorkingCosList Left Join ROW_Id_Adp_Id_Cross_Link
on WorkingCosList.Filenum=ROW_Id_Adp_Id_Cross_Link.Filenum
where ROW_Id_Adp_Id_Cross_Link.Filenum is null
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34898428
SELECT a.* FROM WorkingCosList a LEFT JOIN ROW_Id_Adp_Id_Cross_Link b ON a.fileNum = b.fileNum
WHERE IsNull(b.filenum);
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34898436
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
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34898670
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now