[AccessD] Mailing Labels

...as in the following from my "how to aggregate rows" sample code 
collection:

' this creates a table named "tblOriginal" and populates it with normalized 
data
' then it makes a copy of that table and aggregates the "number" field for
' matching "names".
'

Public Function FixTable() As Boolean
On Error Resume Next

Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset

Dim strColumn1 As String
Dim strColumn2 As String

Set db = CurrentDb()

' pause for user to step through code
Stop

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblOriginal'") = 1 Then
    DoCmd.DeleteObject acTable, "tblOriginal"
End If

strSQL = "CREATE TABLE tblOriginal (Column1 Text(10), Column2 Text(10)) "
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','1')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','2')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','1')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','2')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','3')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('C','1')"
db.Execute strSQL

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblCopy'") = 1 Then
    DoCmd.DeleteObject acTable, "tblCopy"
End If

' Create Temp Table
strSQL = "SELECT Column1, Column2 INTO tblCopy " _
       & "FROM tblOriginal WHERE 1 = 0;"
db.Execute strSQL

strSQL = "SELECT Column1, Column2 FROM tblOriginal " _
       & "ORDER BY Column1, Column2 ASC"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
    If Not .BOF And Not .EOF Then .MoveFirst

    strColumn1 = !Column1
    strColumn2 = !Column2

    .MoveNext

    Do Until .EOF
        If strColumn1 = !Column1 Then
            strColumn2 = strColumn2 & ", " & !Column2
        Else
            strSQL = "INSERT INTO tblCopy (Column1, Column2)  " _
                         & "VALUES('" & strColumn1 & "','" & strColumn2 & 
"')"
            db.Execute strSQL
            strColumn1 = !Column1
            strColumn2 = !Column2
        End If
        .MoveNext
    Loop

    ' Insert Last Record
    strSQL = "INSERT INTO tblCopy (Column1, Column2)  " _
                 & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
    db.Execute strSQL

    .Close
End With

Set rst = Nothing
Set db = Nothing

DoCmd.OpenTable "tblCopy"

End Function

...hth Martin

William

----- Original Message ----- 
From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Wednesday, June 21, 2006 10:45 AM
Subject: Re: [AccessD] Mailing Labels


> Martin:
>
> I'd process the list with a bit of code and create a temp table with the
> text you need, rather than try to make the queries jump through that
> hoop.  Although I'm sure it could be done quite cleverly with queries
> and report features.  I'm just much better with code than queries and
> find it takes a lot less time to make temp tables for oddball reports.
>
> Rocky
>
>
> Martin Reid wrote:
>> Given the following data
>>
>> 1234,Name,Address
>> 1234,Name,Address
>> 1234,Name,Address
>> 1234,Name,Address
>> 2345, Name Address
>> 2345,Name Address
>>
>> I need to get one mailing label produced
>>
>> 1234, Name1, Name2,Name3,Name4
>> Address
>>
>> 2345,Name1,Name2
>> Address
>>
>> Etc
>>
>> So I can have multiple people at the same address. I need the name line 
>> to include all the names but only produce 1 label per address.
>>
>> Martin
>>
>>
>> Martin WP Reid
>> Training and Assessment Unit
>> Riddle Hall
>> Belfast
>>
>> tel: 02890 974477
>>
>>
>
> -- 
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>