Solved

query to find Unmatched records

Posted on 2011-02-15
4
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to send a gZip File From My PC to a Server through a URL From VBA in MS Access 8 44
Save Selections in MS Access 3 31
ORDER BY 7 39
Error 438 6 18
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
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…

730 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