Need to compare 2 tables

[Guest]

I am doing a mail response analysis and have to compare a responder file to a
housefile. Can Access 2003 cpmpare on Last name, First Name, Address 1, and
Zip and count the matches. If so, how? Thanks

 

[Guest]

As long as the data is entered *exactly* the same in each table, you can
create a new query that includes the two tables. Drag a join from each field
in one table to it's similarly named field in the other table, for example
LastName to LastName, FirstName to FirstName, etc. The join should be the
default Inner Join. Add at least one field from either table to the QBE grid.
When you run this query, it should return records where the values are exact
matches.

Then convert the query to a totals query, and change the Group By to Count.
Add a suitable alias to the field that you are displaying. A column alias is
separated from the field name with a colon in query design view, and occurs
on the left side. The corresponding SQL statement uses the AS keyword to show
the alias.

Try the following query in the sample Northwind database. First, create a
copy of the Employees table (structure + data). Name this table "Employees2".
Change some data in one or both tables, keeping track of how many records you
have changed. Create a query with the following SQL statement:

SELECT Count(Employees.City) AS [Matching Records]
FROM Employees
INNER JOIN Employees2
ON (Employees.PostalCode = Employees2.PostalCode)
AND (Employees.Address = Employees2.Address)
AND (Employees.FirstName = Employees2.FirstName)
AND (Employees.LastName = Employees2.LastName);

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Douglas J. Steele]

The SQL would be something like:

SELECT Count(*)
FROM Table1 INNER JOIN Table2
ON Table1.LastName = Table2.LastName
AND Table1.FirstName = Table2.FirstName
AND Table1.Address1 = Table2.Address1
AND Table1.Zip = Table2.Zip

To do this via the graphical query builder, add both tables to the designer.
If there are no lines joining the tables, drag the LastName field from
table1 to table2. Do the same for the FirstName, Address1 and Zip fields so
that there are 4 lines between the 2 tables. (If there were already lines
joining the 2 tables, ensure you only have the 4 above).

Drag the * from the top of one of the tables (it doesn't matter which one)
into the Field row of the grid. Change the query to a Totals query (it's an
option under the View menu). Change the entry in the Total row from Total to
Count.

 

[Brendan Reynolds]

I am doing a mail response analysis and have to compare a responder file to
a
housefile. Can Access 2003 cpmpare on Last name, First Name, Address 1,
and
Zip and count the matches. If so, how? Thanks

Well, yes, but ...

Yes, you can count the matches with a query such as the following ...

SELECT Count(*) AS Dups
FROM Table1 INNER JOIN Table2 ON (Table1.Zip = Table2.Zip) AND
(Table1.[Address 1] = Table2.[Address 1]) AND (Table1.[Last Name] =
Table2.[Last Name]) AND (Table1.[First name] = Table2.[First name]);

.... but tables of names and addresses are likely to contain all kinds of
various spellings and abbreviations of the same name and address, e.g. "Mr.
John Smith, 1 Main Street", and "Mr Jack Smith, 1 Main St.", and there is no
simple way to detect those.