Exact match query

[Guest]

My colleague told me that there's a function in sccess that can accomplish
this.

I need to check every month's report against the last. As I don't want to
waste time checking those entries that were checked previously, I want to run
a query to find the unmatched entries (comparing 2 tables). However, I don't
have a primary key as these tables are imported from excel. I need to find an
exact match for every cell in the row.

I've tried running the unmatched query on access and it only matched the
data in 1 column. How can I go about it? Is there a way to do it in excel?

Any form of help is appreciated. Thanks.

 

[John Spencer]

How many fields are involved? Do the fields ALWAY have a value assigned or
are some of them blank at times?
Assumptions:
four fields in the tables
No blank fields the tables

To find all records in TableA that have no match in TableB the SQL statement
would look like:

SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON A.Field1 = B.Field1
AND A.Field2 = B.Field2
AND A.Field3 = B.Field3
AND A.Field4 = B.Field4
WHERE B.Field1 is Null

In the query grid,
-- Add both tables
-- establish a relation between the tables for each pair of fields
---- Drag from TableA.Field1 to TableB.Field1
-- Double click on each relation and choose ALL in A and only matching in B
-- Select all the fields from tableA
-- Select any field from tableB and set its criteria to Is Null

Reverse the process to find all records in tableB that have no match in
TableA.

The problem becomes more complex if some of the fields can contain blanks
and if more than 10 fields are involved in the matching.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I have 8 fields but I only need to match 3 of the fields.. I need them to be
the same. So, am I right to say that I only have to join those 3 fields?

 

[John Spencer]

If you only need to compare three fields then you only need to join on the
three fields.

Use an INNER JOIN to get records that match in both tables
Use a LEFT JOIN to get records in one table that is not in the other table
(see my example)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

So I should use a LEFT JOIN now right?

Thanks for your help! (",)