Address labels: prevent duplicate mailings

  • Thread starter Thread starter [Cherub]
  • Start date Start date
C

[Cherub]

Greetings,

Does anyone have an elegant solution to the problem of producing a single
address label for a family when there are multiple records in the database
for each family member.
 
Find duplicate query.

I suggest making a (copy) of a table with all the data you want to
print, then running a find duplicate query against it and deleting the dups
(base it on the address fields, address city and state - the last can be
replaced with zip if you know all the zips are correct and in the same
format)

I seem to recall a better solution somewhere, but I have never had the
problem.
 
Greetings,

Does anyone have an elegant solution to the problem of producing a single
address label for a family when there are multiple records in the database
for each family member.
Click to expand...

Use the DISTINCT predicate when building your query for the labels. You
still will get duplicates if the data is not consistent (Joe Smith is not
Joseph Smith. is not Joe P. Smith). Anyway, the syntax is:

Select Distinct FirstName, LastName, Address, City, State, Zip From
tblPeople
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin Meyer said:
Use the DISTINCT predicate when building your query for the labels. You
still will get duplicates if the data is not consistent (Joe Smith is not
Joseph Smith. is not Joe P. Smith). Anyway, the syntax is:

Select Distinct FirstName, LastName, Address, City, State, Zip From
tblPeople
--
Click to expand...

I thought there was a better way than mine.

I don' think there will be a problem with Joe and Joseph, as the writer
was looking at multible individuals at the same address. However "Street"
or "St" or St." will cause problems.

BTW from the help file:

Prevent showing duplicate records in a query
Microsoft Access considers a record to be unique when a value in any field
in a record differs from the value in the same field in any other record. In
a query, you aren't necessarily displaying all the fields that make up the
records in the underlying tables or queries. Therefore, if the field that
distinguishes one record from another isn't in the query design grid, the
query's results can appear to include duplicate records.
For example, if one of the underlying tables for a query is an Employee
table, and you add only the LastName and Country fields to the query design
grid, it might appear that you have duplicate records when several employees
have the same last name and live in the same country/region. However, the
records are not duplicates in the underlying table because the EmployeeID is
unique for each record.
Open a query in Design view.
Select the query by clicking anywhere in query Design view outside the
design grid and the field lists.
Click Properties on the toolbar to display the query's property sheet.
Do one of the following:
Prevent showing duplicate records in a query based on fields in the
underlying table or query
Set the UniqueRecords property to Yes.
Prevent showing duplicate records in a query based on fields in the query
design grid
Set the UniqueValues property to Yes.
Notes
The UniqueRecords property has an effect only when you use more than one
table in the query and select fields from those tables. When the
UniqueRecords property is set to Yes, Microsoft Access automatically sets
the UniqueValues property to No.
When the UniqueValues property is set to Yes, Microsoft Access automatically
sets the UniqueRecords property to No.
 
My solution is to create a separate table for family (really for residence)
then add a foreign key field to each individual record which indicates the
family (or residence) to which they belong. Residence is really the correct
term here since a family could have members living at different residences
(not to mention the fact that some people may have multiple addresses) but
for most applications, family suffices. Whichever you choose, this is the
table for the address information.

Once this is properly normalized, running a query which selects only one
record per family is a snap.
 
Arvin,

I used your example and it works fine with the fields that I used for dupes.
What is the rest of the syntax to include all the fields in the table while
checking dupes against only 4 fields? Here is my SQL:

Select Distinct FN, LN, ADD1, CTY From tblDonor01

I need to add 13 other fields that are not used for calculating dupes. I
assume I should run a make table query and create a new table without the
dupes?

Best regards,
Scott B
 
Back
Top