Solved

Finding Unmatched Query in Access

Posted on 2004-09-30
10
519 Views
Last Modified: 2008-03-04
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
Comment
Question by:isaacr25
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12193831
How do you determine that the records are duplicates?  One field (maybe a primary key), many fields, something else...?
0
 

Author Comment

by:isaacr25
ID: 12193856
I have three fields that make up the composite primary key (Date, Time, Location).
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12193906
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 12

Expert Comment

by:pique_tech
ID: 12193920
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
 

Author Comment

by:isaacr25
ID: 12194059
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
 
LVL 12

Accepted Solution

by:
pique_tech earned 125 total points
ID: 12194180
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12194215
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
 

Author Comment

by:isaacr25
ID: 12194219
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12194274
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
 

Author Comment

by:isaacr25
ID: 12194390
Never mind...
    I had "tbl" in front of the table names. Taking that off did the trick. Thanks.

Isaac
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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