Address label query

[headlock]

Hi All, I want to have a query that creates address labels from my contact list. How do I do that without duplicate labels? I have 2 or 3 children in one family and a separate record for each child but only want to send one mail out per family. Thanks for your help.

 

[Trevor G]

Welcome to the Forum.

This would depend on how you are storing your data. You could use the home address in a query and then group based on this and this would only show the address once.

 

[willknapp]

If you wanted to get really fancy, you could develop a query that would identify if there are multiple records, and if so, set the first line of the address to "The Jones Family" rather than simply selecting a name from one of the records.

But Trevor is right - a lot depends on how your data is stored. If you post the fields and tables (if more than one) you're using, I'll write up a query you could use.

 

[willknapp]

Hi All, I want to have a query that creates address labels from my contact list. How do I do that without duplicate labels? I have 2 or 3 children in one family and a separate record for each child but only want to send one mail out per family. Thanks for your help.

Here's a very simple solution to what I was talking about. First, develop an aggregate query based on your Contact table, that groups by all fields except first name (which only returns the value from the first record using the FIRST aggregate function) and then calculates the total number of records for each grouping (using the COUNT aggregate function).

SELECT First(tblTest.txtFirstName) AS FirstName, tblTest.txtLastName AS LastName, tblTest.txtAddress AS Address, tblTest.txtCity AS City, tblTest.txtState AS State, tblTest.txtZip AS Zip, Count(tblTest.txtLastName) AS multiple

FROM tblTest

GROUP BY tblTest.txtLastName, tblTest.txtAddress, tblTest.txtCity, tblTest.txtState, tblTest.txtZip;
​

---

Next, base a query off of THAT query which, if the count is one (meaning there was a single record in your original table) concatenates FirstName and LastName (with a space between) as the "To" field. If the count is greater than one (meaning multiple records), then the "To" field will be "The " & LastName & " Family".

You can create both of these queries in Access and simply base one on the other, or you can create a query/subquery SQL statement - here's what that would look like (with the original query in blue):

SELECT IIf([multiple]>1,"The " & [lastname] & " Family",[FirstName] & " " & [LastName]) AS [To], Address, City, State, Zip
FROM (SELECT First(tblTest.txtFirstName) AS FirstName, tblTest.txtLastName AS LastName, tblTest.txtAddress AS Address, tblTest.txtCity AS City, tblTest.txtState AS State, tblTest.txtZip AS Zip, Count(tblTest.txtLastName) AS multiple
FROM tblTest
GROUP BY tblTest.txtLastName, tblTest.txtAddress, tblTest.txtCity, tblTest.txtState, tblTest.txtZip);
​

---
Hope this helps!

(Apologies to SQL formatting gurus out there - I'm kind of a simpleton when it comes to such things...)