Want one mailing label per person, but query can have multiple records per person

[Bob Richardson]

I think I need to use the Last() or First() method, but I'm not sure how
that would work. I want to end up with a query that has just one record per
person.

 

[Roger Carlson]

Instead of using an aggregate query, I would use the DISTINCT predicate in
your SQL statement. For instance, suppose this is your query:

SELECT FirstName, LastName, Address, City, ST, Zip from tblAddresses

you would change it to:

SELECT DISTINCT FirstName, LastName, Address, City, ST, Zip from
tblAddresses

All of the records that have duplicates based on the fields in the field
list will be removed. BTW, this can also be done by setting the Unique
Values property to Yes in the Query Builder.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

I think I need to use the Last() or First() method, but I'm not sure how
that would work. I want to end up with a query that has just one record per
person.

Last() is pretty nearly completely useless, and First() isn't much
better: they return the last and first records IN DISK STORAGE ORDER,
which is pretty much arbitrary.

I think instead you should view the query's Properties (right
mouseclick in the grey area behind the tables and select Properties),
and set the Unique Values property of the query to Yes. This will give
you one record for each combination of the selected fields. Don't
include the Primary Key field or any other field that will have
multiple values per person.

John W. Vinson[MVP]

 

[Guest]

The DISTINCT predicate will return one instance where all of the fields are
identical. But what if you have Jon Smith and Jonathan Smith at the same
address, or the same name at 123 First st. and 123 1st ST, or the same
name/address at ZIP 12345 and 12345-6789. Eliminating duplicates from a
mailing list is a black art for which professional mailers take big bucks.

 

[Bob Richardson]

Last() is pretty nearly completely useless, and First() isn't much
better: they return the last and first records IN DISK STORAGE ORDER,
which is pretty much arbitrary.

I think instead you should view the query's Properties (right
mouseclick in the grey area behind the tables and select Properties),
and set the Unique Values property of the query to Yes. This will give
you one record for each combination of the selected fields. Don't
include the Primary Key field or any other field that will have
multiple values per person.

Setting UniqueValue to yes or no didn't help. I still get multiple records
per person.

My query is between a Contact table and a ClassSignUp table. People can sign
up for multiple classes every year. I want to print a mailing list, one for
each person, for all those who have signed up for one or more classes in the
year.

 

[John Vinson]

Setting UniqueValue to yes or no didn't help. I still get multiple records
per person.

My query is between a Contact table and a ClassSignUp table. People can sign
up for multiple classes every year. I want to print a mailing list, one for
each person, for all those who have signed up for one or more classes in the
year.

Uncheck the "Show" checkboxes in the query grid for the ClassSignUp
table. Since these values won't be printed on your mailing labels, you
only need to include them for criteria - not as part of the query.

If this is confusing, please open the Query in SQL view and copy and
paste the SQL text to a message here.

John W. Vinson[MVP]