Match 2 Table Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two Excel Spreadsheets which I want to compare. I have imported them
in Access. I made the primary key in both tables an auto incremental field.
I want to match the records on SSN and Effective Date Begin - not the primary
key. Spreadsheet A has data pulled by operator 1 and spreadsheet B has data
pulled by operator 2. The operators did not pull the exact same data but
parts of each are needed to load into a new database. I want to match the
data from both tables and have a column indicating the record either exists
in both spreadsheets or the record is only in spreadsheet "?". There are
40383 records in Table A and 35351 records in Table B. When I do a join I
get 40408 records and they all have a match so I know I am not doing
something correctly.
 
Hi,


An inner join will create more record than in any of the table if there are
duplicated values in the table, and will only return matches.


{a, b, b, c} inner join {a, b, c, c } will produce 5 records. (pseudo
syntax, just for illustration).


To get unmatched rows, you need to use an outer join. To get a unmatched row
from both tables, you can use:


SELECT a.*, b.*
FROM a LEFT JOIN b ON a.something = b.something
UNION ALL
SELECT a.*, b.*
FROM a RIGHT JOIN b ON a.something = b.something
WHERE a.something IS NULL



but you will still get duplicated rows "amplification". You can remove
duplicated rows with a DISTINCT or a GROUP BY.




Hoping it may help,
Vanderghast, Access MVP
 
I don't know much about Access. Where do I put the Select clause? I opened
the query in design and added the two tables. I joined them by SSN &
Effective Date. Where do I enter the statement?
 
Hi,


since it is a UNION query, you have to do it in the SQL view, but you can
also do it in smaller steps, using the graphical environment for the more
problematic parts.

We will make 3 queries.

The first one, bring your two tables, join them as you did, but next, right
click on the line representation of the join and, from that context menu,
use option 2. Repeat for each line making the join. Bring the required
fields in the grid. Save that query, qu1.

The second query starts the same way, but this time, use option 3, for all
the lines of the join. Bring the same fields as for qu1 in the grid. If one
of the field with an arrow pointing to it (in the first table) is in the
grid, add the criteria: IS NULL. If no field with an arrow pointing to it
from the join is in the grid, bring one, uncheck the Show check box, and add
the criteria IS NULL under it. Save that query, qu2.


In the last query, you have to move into an SQL view. You can switch to the
SQL view from the first button on the default toolbar. The typing you have
to do is:

SELECT * FROM qu1
UNION
SELECT * FROM qu2



save it as qu3. Use qu3.


Just for sanity purpose, the first query could look like, in SQL view:

SELECT tableA.f1, tableB.f2
FROM tableA LEFT JOIN tableB ON tableA.SSN = tableB.SSN AND
tableA.EffectiveDate = tableB.EffectiveDate


the second query could look like, in SQL view:

SELECT tableA.f1, tableB.f2
FROM tableA RIGHT JOIN tableB ON tableA.SSN = tableB.SSN AND
tableA.EffectiveDate = tableB.EffectiveDate
WHERE tableA.SSN IS NULL





Hoping it may help,
Vanderghast, Access MVP
 
When you select 'New' in the Query window in Access (A97 at least),
you can choose to open wizards for creating queries for 'Finding
Duplicates' or 'Finding Unmatched':

Finding duplicate in column 'DuplicateColumn' in Table1:

SELECT *
FROM Table1
WHERE DuplicateColumn In (SELECT DuplicateColumn FROM Table1 As Tmp GROUP BY DuplicateColumn HAVING Count(*) >1)
ORDER BY DuplicateColumn


Finding unmatched records in column 'ColName' in Table1 and Table2:

SELECT A.ColName
FROM Table1 A LEFT JOIN Table2 B ON A.ColName = B.ColName
WHERE B.ColName Is Null

hth
PerL
 
Back
Top