Solved

Finding Unmatched Query in Access

Posted on 2004-09-30
10
518 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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