Solved

Finding records in one able but not another.

Posted on 2004-10-15
8
271 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 350 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 150 total points
ID: 12320164
In Access you can use the UNMATCHED query wizard (Queries/New/Find UnMatched)

AW
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

696 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