I have a table of approximatly 60,000 records. The records were created from
product registrations. I need to create a mailing list, but the same person
may have registered more than one product, and more than one person in a
household may have registered a product. I only want to send one letter to
each address. I made a query with only addresses and then grouped themby
address, city, state. then I joined the table with my grouped query. I
ended up with more records than I started with. The grouped query looks
good. I am green at this, and must be doing the join incorrectly. Can/will
anyone walk me through this, please?
To get a list of addresses only you can return distinct rows with a
query which includes columns for the address data only (address, city
and state in your case by the sounds of it). The query would be
something like this:
SELECT DISTINCT address, city, state
FROM YourTable;
In query design view you achieve the same thing by setting the query's
UniqueValues property to True (Yes in the properties sheet).
This won't include the addressee's names of course. One option would
be to include one name randomly from each address by grouping the
query on the address columns and returning the MAX name (any
aggregation operator will do in fact as its merely to get one name
arbitrarily for each address). If the name is in a single column,
e.g. customer, you can get the MAX value of that column per address
like so:
SELECT MAX(customer) AS Addressee,
address, city, state
FROM YourTable
GROUP BY address, city, state;
If the names are separated into FirstName and LastName columns (as
they should be) then you cannot independenly get the MAX values of
each as you'd end up in many cases with the wrong first and last name
paired. You have to concatenate each name into a single value and get
the MAX value of that:
SELECT MAX(TRIM(FirstName & " " & LastName)) AS Addressee,
address, city, state
FROM YourTable
GROUP BY address, city, state;
If you want to create return a single row which includes everybody at
that address then it gets more complex as you'd need to call a VBA
function which concatenates all the names into a single string,
separating each with something like a semi-colon or a carriage return/
line feed, e.g. using a semi-colon:
Public Function GetAddressees(strAddress As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAddressees As String
strSQL = "SELECT DISTINCT TRIM(FirstName & " " & LastName) " & _
"FROM YourTable " & _
"WHERE Address & city & state = """ & strAddress & """"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do While Not .EOF
strAddressees = strAddressees & "; " & _
TRIM(.Fields("FirstName") & " " & .Fields("LastName"))
.MoveNext
Loop
.Close
' remove leading semi colon and space
strAddressees = Mid$(strAddressees, 3)
End With
GetAddressees = strAddressees
End Function
If you want to insert a carriage return/line feed after each name
change it like so:
Do While Not .EOF
strAddressees = strAddressees & vbNewLine & _
TRIM(.Fields("FirstName") & " " & .Fields("LastName"))
.MoveNext
Loop
In the query you'd call the function like so:
SELECT DISTINCT
GetAddressees([address] & [city] & [state]) AS Addressees,
address, city, state
FROM YourTable;
Ken Sheridan
Stafford, England
