Solved

query to find Unmatched records

Posted on 2011-02-15
4
389 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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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