Merge two or more tables into new table

Y

yourokay

We are using access to store customer lists from which mailing labels are
printed and/or mail merges are done. We want to be able to use a query to
combine two or more of the lists on demand to make a new list that eliminates
duplicates. How can we do this?
 
J

John Spencer (MVP)

Try looking at using a UNION query, but I think you will be disappointed.
Unless you have really tight, rigid rules on data entry you will still have lots
of duplicates. To the human they are duplicates, to the computer they are not.

John Spencer 8666 Copper Court CityScape, CO 12345
John P Spencer 8666 Copper Court CityScape, CO 12345
J P Spencer 8666 Copper Court CityScape, CO 12345
John Spencer 8666 Copper Ct CityScape, CO 12345
John Spencer 8666 Copper Ct City Scape, CO 12345
John Spencer 8666 Copper Ct CityScape, CO 12345-2345
John Spencer 8666 Copper Ct CityScape, CO 12345
John Spencer Jr 8666 Copper Ct CityScape, CO 12345

Which of the above are duplicates? NONE to the computer, probably all to the human.

Deduping mail addresses is an expensive business for a very good reason - it is hard.

Combine the lists this way (You can't use the grid to build the query, you must
type the query text)

SELECT NameField, AddressField, CityField, StateField, ZipCode
FROM ListA
UNION
SELECT NameField, AddressField, CityField, StateField, ZipCode
FROM ListB

That will eliminate any records that are exactly the same in all five fields.
 
Y

yourokay

Thanks John. Our lists are extracts from our exchange server, so we could
avoid problems by re-extracting the lists each time we run labels or do a
mail merge. Can we use the union merge to merge more than two tables in one
pass? How do we write the query to prompt for the table names, or give a
drop down list to the user? I've seen how to prompt for parameters in the
query grid, but not in SQL code.
 
J

John Spencer (MVP)

Yes you can combine more than two tables.

You can't do it with a parameter. Parameters are for criteria only.

IF I needed to do this I would probably write a vba routine and pass it the
names of the tables to be unioned. Then the VBA could build the SQL statement
and then you could use that to set the SQL of a query. It would require that
you have the same field names in each list, but since you are extracting them
from the same source, I would think it wouldn't be a problem

UNTESTED AIR CODE for building the SQL String; You can use the string to build
a query def, populate a recordset, etc.

Public Function MakeSQL (ParamArray aTableNames()) As String
Dim I as Integer, strSQL As String


For I = LBound(aTableNames) to UBound(aTableNames)
StrSQL = StrSQL & "SELECT NameField, AddressField" & _
", CityField, StateField, ZipCode" & _
"FROM " & aTableNames(I) & " UNION "
Next I

If Len(StrSQL) > 0 Then
StrSQL = Left(StrSQL,Len(StrSQL)-7)
End If

MakeSQL = StrSQL

End Function
Thanks John. Our lists are extracts from our exchange server, so we could
avoid problems by re-extracting the lists each time we run labels or do a
mail merge. Can we use the union merge to merge more than two tables in one
pass? How do we write the query to prompt for the table names, or give a
drop down list to the user? I've seen how to prompt for parameters in the
query grid, but not in SQL code.
--
Click to expand...
 
Top