Scenario:

I need to mail to clients that have more than one account with us.  My
records are laid out as follows:

SS#    Address    City     State    Zip    Account#

where SS# is the same and there can be 5 or more accounts for each social
security number.  I need to put the next account number into a blank place
holder or the next open cell and get rid of the duplicate information.  The
letter will list the account numbers that each client has so all of the
account numbers have to be in ONE record in its own field.

Does anyone know if this is possible or if there is a better way to do it
rather than pay a mailhouse a fortune or purchase software that does this
function for thousands of $$$$??

Any help would be GREATLY appreciated!

M. O'Connell

Let me try to understand your question: you want for each distinct client,
identified by its SSN, one row with:

SSN, Address, City, State, Zip + A concatenation of the Account field.

In a standard module, write this function:

Function ConcatAccount(strSSN As String) As String
    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select Account from tblSSN " & _
        "where SSN = '" & strSSN & "'", dbOpenSnapshot)

    ConcatAccount = "Account(s): "

    With rs
        .MoveFirst
        Do
            ConcatAccount = ConcatAccount & !Account & ", "
            .MoveNext
        Loop Until .EOF
        .Close
    End With

    ConcatAccount = Left$(ConcatAccount, Len(ConcatAccount) - 2)
    Set db = Nothing
    Set rs = Nothing
End Function

The above function makes a few assumption:
1. The SSN number is actually a string, not numeric. I don't know a thing about
SSN in your region - presumably in the USA. If it is numeric, rather then a
string, you don't need the single quotes in the select statement, surrounding
the SSN.

2. It also assumes the actual field names don't contain the # character. If
they do, put the field names between square brackets, i.e. [SS#], otherwise
Access may think you are trying to pass a date field or parameter.

3. General advice: if you have many clients, index the SSN, whether it's a
string or not, or the query will be inordinately slow.

Now, write a query using the above function:

SELECT SSN, Address, State, City, Zip, ConcatAccount([SSN]) AS Accounts
FROM tblSSN
GROUP BY SSN, Address, State, City, Zip;

Set the query type to snapshot. It's not updatable anyway, and it will run
faster. If, for some reason, one of the fields Address, State, City or Zip are
not identical for a given SSN, this query will not return the expected result.
Use the First function in that case. Btw, this should also point you to a
design flaw: the Account field does not belong in the same table with the other
data, but in separate table, related many to one with the tblSSN.

Hth,
Radu Lascae