Solved

Finding Unmatched Query in Access

Posted on 2004-09-30
10
516 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

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

19 Experts available now in Live!

Get 1:1 Help Now