Finding records in one able but not another.

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

LVL 3
gillgatesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
broesiConnect With a Mentor Commented:
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
 
PreachDotNetCommented:
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
 
gillgatesAuthor Commented:
what should be in the criteria variable?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
PreachDotNetCommented:
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
 
broesiCommented:
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
 
PreachDotNetCommented:
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
 
gillgatesAuthor Commented:
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
 
Arthur_WoodConnect With a Mentor Commented:
In Access you can use the UNMATCHED query wizard (Queries/New/Find UnMatched)

AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.