Solved

Finding Unmatched Query in Access

Posted on 2004-09-30
10
517 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to save new report from old one 9 28
type of query 11 41
What's wrong with CopyFromRecordset 6 33
A Function to parse a text string 4 34
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

786 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