?
Solved

query to find Unmatched records

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…
Suggested Courses

616 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