?
Solved

Finding records in one able but not another.

Posted on 2004-10-15
8
Medium Priority
?
272 Views
Last Modified: 2012-06-21
I want to find all records that are in one table but not in the other.... What I mean is I want to find records that do not completely match and records that do not exists.

For example... record one of tblOne has 8 hours, but in tblTwo it has 7.5

Record 11 & 12 of tblOne are completely missing from tblTwo.

I am open to doing this using two queries instead of just one.


tblOne
------------------------------------------------------
ID            Personnel      Date      Hours
1            00003741      10/7/2004      8
2            00010078      10/7/2004      8
3            00010078      10/8/2004      8.1
4            00010079      10/7/2004      8
5            00010082      10/7/2004      8
6            00010082      10/8/2004      8
7            00010083      10/7/2004      9.7
8            00010083      10/8/2004      10.2
9            00010084      10/7/2004      8
10            00010084      10/8/2004      8.1
11            00010085      10/7/2004      8.3
12            00010085      10/8/2004      7.9
13            00010087      10/7/2004      8

tblTwo
----------------------------------------------------------
ID                        Personnel      Date      Hours
1            00003741      10/7/2004      7.5
2            00010078      10/7/2004      8
3            00010078      10/8/2004      8.1
4            00010079      10/7/2004      8
5            00010082      10/7/2004      8
6            00010082      10/8/2004      8
7            00010083      10/7/2004      9.7
8            00010083      10/8/2004      10.2
9            00010084      10/7/2004      8
10            00010084      10/8/2004      8.1
11            00010087      10/7/2004      8
12            00010087      10/8/2004      8
13            00010088      10/7/2004      8

0
Comment
Question by:gillgates
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:PreachDotNet
ID: 12319777
http://www.ss64.com/ora/union.html
dim sql as string
sql = "SELECT * FROM tblOne WHERE mField = " & criteria
sql2 = "SELECT * FROM tblOne WHERE mField = " & criteria

dim finalSQL as string
finalSQL = sql & " MINUS " & sql2
0
 
LVL 3

Author Comment

by:gillgates
ID: 12319803
what should be in the criteria variable?
0
 
LVL 6

Expert Comment

by:PreachDotNet
ID: 12319896
Sorry, that works in Oracle, in Access you can nest sub queries
http://www.simply-access.com/SimplyAccess_Tips-Deleting-Duplicate-Records-Microsoft-Access.html

Select * From tblOne Where [IDField] NOT IN ( SELECT [IDField] From tblTwo)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:broesi
ID: 12319919
MINUS is not supported in Access.

You can create 2 queries with an left/right outer join on the fields you need to check for differences:


For instance:

select tbl1.* from tbl1 left out join tbl2 on tbl1.ID=tbl2.id
where tbl2.id is null

will give you all records from tbl1 that are not in tbl2.

Respectively

select tbl2.* from tbl1 right out join tbl2 on tbl1.ID=tbl2.id
where tbl1.id is null

will give you all records from tbl2 that are not in tbl1.

Use a UNION on these two queries to get the complete picture.

To find different values use something like this:

select *
from tbl1
inner join tbl2 on tbl1.id=tbl2.id and tbl2.value<>tbl1.value

You can also add this to your UNION-query.

HTH,

broesi
0
 
LVL 6

Expert Comment

by:PreachDotNet
ID: 12319932
Sorry, that will find your missing records.  To find records that arent matched would be

SELECT * FROM tblOne, tblTwo Where tblOne.Hours <> tblTwo.Hours

You will need to add a join between the Unique IDs on both tables
0
 
LVL 3

Author Comment

by:gillgates
ID: 12319961
Sorry guys, I messed up the  data....

There is no ID field... the primary key of each table is Personnel & Date


tblOne
------------------------------------------------------
Personnel     Date     Hours
00003741     10/7/2004     8
00010078     10/7/2004     8
00010078     10/8/2004     8.1
00010079     10/7/2004     8
00010082     10/7/2004     8
00010082     10/8/2004     8
00010083     10/7/2004     9.7
00010083     10/8/2004     10.2
00010084     10/7/2004     8
00010084     10/8/2004     8.1
00010085     10/7/2004     8.3
00010085     10/8/2004     7.9
00010087     10/7/2004     8

tblTwo
----------------------------------------------------------
Personnel     Date     Hours
00003741     10/7/2004     7.5
00010078     10/7/2004     8
00010078     10/8/2004     8.1
00010079     10/7/2004     8
00010082     10/7/2004     8
00010082     10/8/2004     8
00010083     10/7/2004     9.7
00010083     10/8/2004     10.2
00010084     10/7/2004     8
00010084     10/8/2004     8.1
00010087     10/7/2004     8
00010087     10/8/2004     8
00010088     10/7/2004     8
0
 
LVL 5

Accepted Solution

by:
broesi earned 1400 total points
ID: 12320017
Change the join expressions to something like this:

select * from
tbl1
left outer join tbl2 on tbl1.Personnel=tbl2.Personell and tbl1.Date=tbl2.Date
where tbl2.personnel is null

and

select * from
tbl2
right outer join tbl2 on tbl1.Personnel=tbl2.Personell and tbl1.Date=tbl2.Date
where tbl1.personnel is null


and


select *
from tbl1
inner join tbl2 on tbl1.personnel=tbl2.personnel
and tbl1.date=tbl2.date and tbl2.value<>tbl1.value


broesi
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 600 total points
ID: 12320164
In Access you can use the UNMATCHED query wizard (Queries/New/Find UnMatched)

AW
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …
Suggested Courses

800 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