?
Solved

Find unmarched records based on multiple columns

Posted on 2014-09-06
5
Medium Priority
?
239 Views
Last Modified: 2014-09-07
I have a staging table and a permanent table.  I need to determine if there are unmatched records in the staging table.  I tried using ACCESS 2010 query wizard, but that did not work.  I tried modifying the query, but that did not work.  So, how to I check two tables for unmatched records based on the corresponding 10 columns between them?
0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 2000 total points
ID: 40307992
In other words, you want to find out what records exists in permanent table but not in staging table?

If my assumption is correct, then try this:

SELECT *
FROM   PermanentTable
WHERE  NOT EXISTS
  (SELECT *
   FROM   StagingTable
   WHERE  StagingTable.ID= PermanentTable.ID)

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 40308067
the problem is I need to match ten columns, not one.
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40308106
Did you run this code?

What makes you think it is for just one column?
0
 

Author Comment

by:ssmith94015
ID: 40308168
Well, there is no unique identifier and the users can enter multiple records that are virtually the same except for small differences the different fields.  The 10 columns have to be compared as a unit for uniqueness.  I did not create this database but have to work with what the client already has.  I have been doing this for quite a few years, but with databases I created and ensured one identity column and while I have solved this  before, with my time constraints I was hoping for a quick resolution so I do not have to dig through my code library.  I am trying to get about three weeks work done in the next two days and was just hoping for a canned solution.
0
 

Author Closing Comment

by:ssmith94015
ID: 40308606
I got a little sleep and took a look again.  This will work, thank you.  It has helped tremendously in finishing atT least one aspect of this mess.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

771 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