[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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

0
gillgates
Asked:
gillgates
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
broesiCommented:
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
 
Arthur_WoodCommented:
In Access you can use the UNMATCHED query wizard (Queries/New/Find UnMatched)

AW
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now