Solved

Finding records in one able but not another.

Posted on 2004-10-15
8
236 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
  • 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now