Improve company productivity with a Business Account.Sign Up

x
?
Solved

query to find Unmatched records

Posted on 2011-02-15
4
Medium Priority
?
424 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

595 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