[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Finding Unmatched Query in Access

I am trying to create a query which compares two tables (in Access) and returns the records that are not common in both tables. I cannot seem to get the desired results. When I use the "Find Unmatched Query Wizard," the results that I get still include records that are common in both tables. Am I using this correctly?

Also, I have changed the query type to an Append query so that I can append the unmatched data to a table. When I run the query, I keep getting the message that there is a "Duplicate Output Destination." The two tables being compared are identical in structure. Thanks for the help in advance. Thanks.

Isaac
0
isaacr25
Asked:
isaacr25
  • 5
  • 4
1 Solution
 
pique_techCommented:
How do you determine that the records are duplicates?  One field (maybe a primary key), many fields, something else...?
0
 
isaacr25Author Commented:
I have three fields that make up the composite primary key (Date, Time, Location).
0
 
jmacmickingCommented:
The unmatched query wizard only allows you to set one key field between the tables (I believe it defaults to the primary key, if set); it's the only thing that's really checked for matches.  If you need more fields checked for matches you'll have to manually add them.  The easiest way, using the design query view, is to drag the field name from one table to the matching field name on the other table.  Then just double click on the black link line Access draws and change it to show all results from the primary table (and only matched results from the secondary table).  

It also checks in one direction only--so you'll get a list of all the records on Table1 that aren't on Table2, but you'll need a seperate query to get a list of all the records on Table2 that aren't on Table1.

If you post the SQL for the query someone can probably offer more specific help.
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!

 
pique_techCommented:
So let's call the tables tblA and tblB.  To get items in tblA that are not in tblB, do this:

SELECT * FROM tblA WHERE CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) NOT IN
       (SELECT CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) FROM tblB)

To get items in tblB not in tblA, do the reverse:

SELECT * FROM tblB WHERE CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) NOT IN
       (SELECT CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) FROM tblA)

To get all these at one time, then do this (assuming that your tables have the same fields in the same order):
SELECT * FROM tblA WHERE CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) NOT IN
       (SELECT CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) FROM tblB)
UNION
SELECT * FROM tblB WHERE CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) NOT IN
       (SELECT CStr(Date) & "-" & CStr(Time) & "-" CStr(Location) FROM tblA)
0
 
isaacr25Author Commented:
I'm getting a syntax error (missing operator) when I copy and paste the code (getting items in tblB not in tblA). My primary field names are:

Current Date
Current Time
Location/Unit

This is what I entered:

SELECT * FROM tblTemp WHERE CStr(Current Date) & "-" & CStr(Current Time) & "-" CStr(Location) NOT IN
       (SELECT CStr(Current Date) & "-" & CStr(Current Time) & "-" CStr(Location) FROM tblBedCensus)

Thanks again.
0
 
pique_techCommented:
Oops, sorry:

SELECT * FROM tblA WHERE CStr(Date) & "-" & CStr(Time) & "-" & CStr(Location) NOT IN
       (SELECT CStr(Date) & "-" & CStr(Time) & "-" & CStr(Location) FROM tblB)
UNION
SELECT * FROM tblB WHERE CStr(Date) & "-" & CStr(Time) & "-" & CStr(Location) NOT IN
       (SELECT CStr(Date) & "-" & CStr(Time) & "-" & CStr(Location) FROM tblA)

I missed the last ampersand ("&") in my original post.
0
 
pique_techCommented:
Also, since your field names have spaces in them, you'll probably have to enclose them in square brackets:

SELECT * FROM tblTemp WHERE CStr([Current Date]) & "-" & CStr([Current Time]) & "-" & CStr([Location]) NOT IN
       (SELECT CStr([Current Date]) & "-" & CStr([Current Time]) & "-" & CStr([Location]) FROM tblBedCensus)
UNION
SELECT * FROM tblBedCensus WHERE CStr([Current Date]) & "-" & CStr([Current Time]) & "-" & CStr([Location]) NOT IN
       (SELECT CStr([Current Date]) & "-" & CStr([Current Time]) & "-" & CStr([Location]) FROM tblTemp)
0
 
isaacr25Author Commented:
Ok..
    That got rid of the syntax error, but now when I run the query its telling me that the database engine cannot find one of the tables!!!! Any advice? It says it can't find tblB (tblTemp). Thanks so much again!

Isaac
0
 
pique_techCommented:
Can you run
     SELECT * FROM tblTemp
and get results?

If not, can you see tblTemp in Query Designer -> Show Table?  What happens when you try to add it?
0
 
isaacr25Author Commented:
Never mind...
    I had "tbl" in front of the table names. Taking that off did the trick. Thanks.

Isaac
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now