Code for combining household names for mailing labels

[dabowery]

In searching for a solution to code or query for household mailing scenarios?, I've found an archive with a partial solution and no further reference. I am not able to get the syntax correct in the code below and need help getting the code to work. I believe I have the SQL straight. Using A2K on XP; VB novice. Thank you in advance for your consideration.

I need to create mailing labels, one per household, from a table in which each person has a different record but a "family code" field provides the right data to group by.

I have a table [tblMailTest] that includes the first and last name data. I have created a new table called tblAddrName with a family code field (PK) and a field called AddrName in which to store the combined names.

I would like to apply the following rules to update tblAddrName:
1. If there is only one record for the family code, do nothing.
2. If there are two records, and the last name is the same, combine the two as in "John & Mary Smith"
3. If there are two records and the last name is not the same, combine the two as "John Smith & Mary Jones"
4. If there are more than two records, the result should be "The Smith Family".

Private Sub Command0_Click()
Dim strSQL As String
Dim rs As Recordset
Dim rsComb As Recordset
Dim strOut As String
Dim tmpfirst(2) As String
Dim tmplast(4) As String
strSQL = "SELECT tblMailTest.FamID, tblMailTest.FstName, tblMailTest.LstName, Count(tblMailTest.FamID) AS FID From tblMailTest GROUP BY tblMailTest.FamID, tblMailTest.FstName, tblMailTest.LstName HAVING Count(tblMailTest.FamID) > 1;"

Set rs = CurrentDb().OpenRecordset(strSQL)
Set rsComb = CurrentDb().OpenRecordset("tbladdrname")

With rs

If FID = 2 Then

tmplast(1) = LstName tmpfirst(1) = FstName
.MoveNext
tmplast(2) = LstName tmpfirst(2) = FstName

If tmplast(1) = tmplast(2) Then
strOut = (tmpfirst(1) & " and " & tmpfirst(2) & tmplast(1))
Else: strOut = (tmpfirst(1) & tmplast(1) & " & " & tmpfirst(2) & tmplast(2))

End If
End If

If FID > 2 Then

strOut = "The" & LstName & "Family"

End If
rsComb.AddNew rsComb!FamilyID = FamID rsComb!tbladdrname = strOut rsComb.Update rsComb.Close
End Sub

 

[AncientOne]

Your code doesn't work because it's got a lot of syntax errors, you're not actually stepping through the recordset properly and you're just going about it the wrong way.

If your tables are correctly related it shouldn't be necessary to create the new table and put duplicate data into it.

I know you've struggled mightily with this problem for some time and because of its complexity, most of us who are willing to help, lose the plot quite quickly. We need to have some concrete things like tables and relationships to pore over.

I have a sort of ill-formed idea that a field with a code for how you are going to concatenate the names might produce a solution. I just don't know yet.

Why don't you post your db so that we stand a better chance of coming up with a solution?

 

[dabowery]

Thank you AnceintOne. May I email the file to you since it's about 1.4MGs? I am not able to get it down to meet posting requirements.