Concantenating records within a single column

[monkeytunes]

Hey buddays,

I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.

I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.

But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:

first_name | last_name | state_name
John | Johnson | AL, AK, TX, FL
Eddie | Edwards | MA, TX, FL
Sally | Smith | VT, WA, CA, TX

How do I concatenate records from a single column?

 

[monkeytunes]

Waited awhile, thought I'd give it a bump.

Help?

 

[john471]

What do you intend to do with it once you get it into Excel ? That format doesn't seem conducive do further data management.

Here are two possibilities:-
1)
Write a crosstab query and export it to Excel. It may be cumbersome with 50 states though. Using the data and structure you supplied, and ading "another" John Johnson, who operates in CA (to highlight the importance of using the NameID field instead of the name itself) You would end up with something like

NameStateXTAB.jpg, attached below.

TRANSFORM First(qryNamesStates.state_abbrev) AS [The Value]
SELECT qryNamesStates.first_name, qryNamesStates.last_name
FROM qryNamesStates
GROUP BY qryNamesStates.ID, qryNamesStates.first_name, qryNamesStates.last_name
PIVOT qryNamesStates.state_abbrev;

2)
Alternately you could use some VBA to loop through a recordset, and write lines to a file (or XL directly - I've gone with the csv file option) which you can then open directly in Excel.

The below is just a guide based on the (incomplete) information you supplied. You may have to change at least a few things to make it work for you.
a) The name of the query that joins the three tables. I've used "qryNamesStates". Ensure this query includes the ID field from the tblNames table.
b) The name of the ID Field from the tblNames table in the query being used. I've assumed "ID".
c) The name and location of the output file. I've used "C:\MyOutputFile.csv"
d) You may wish to add in some error handling code.

Sub publishNamesStates()
    Dim rs As DAO.Recordset
    Dim lngCurNameID As Long
    Dim szThisNameFirst As String
    Dim szThisNameLast As String
    Dim szThisStateList As String

    Set rs = CurrentDb.OpenRecordset("qryNamesStates")
    
    With rs
        If Not (.BOF And .EOF) Then
            'we have at leat one record
            'go to the fist record
            .MoveFirst
            
            'open the output file for writing
            Open "C:\MyOutputFile.csv" For Output As #1
            
            'ensure the "New Line" trap is entered first time 'round
            lngCurNameID = .Fields("ID").Value - 1
            
            'write out the entire recordset
            Do Until .EOF
                '"New Line" trap
                'Re-Set the static values (per NameID) every time we have a new NameID
                'and re-initialise the state to the first state
                If lngCurNameID <> .Fields("ID").Value Then
                    If Len(szThisStateList) > 0 Then
                        'only write out a line if we have state data
                        'this won't be the case on the very fist iteration
                        'write the line
                        Write #1, szThisNameFirst, szThisNameLast, szThisStateList
                    End If
                    szThisNameFirst = .Fields("first_name").Value
                    szThisNameLast = .Fields("last_name").Value
                    szThisStateList = .Fields("state_abbrev").Value
                Else
                    'This record is a new state for the same person as the last record.
                    'add the state to the list for this person, preceeded by a comma
                    szThisStateList = szThisStateList & "," & .Fields("state_abbrev").Value
                End If
                
                'next record - may or may not be the same person
                lngCurNameID = .Fields("ID").Value
                .MoveNext
            Loop
            'write the final line
            Write #1, szThisNameFirst, szThisNameLast, szThisStateList
            Close #1
            .Close
        End If
    End With
    Set rs = Nothing
End Sub

Will produce a CSV file, contents as follows (not including dashed lines):
-------------------------------------
"John","Johnson","AL,AK,TX,FL"
"Eddie","Edwards","MA,TX,FL"
"Sally","Smith","VT,WA,CA,TX"
"John","Johnson","CA"
-------------------------------------

Which can be opened in XL, and should look like NameStateCSVCode.jpg , attached below.

HTH

Regards John

 

[Cosmos75]

Here's one way to concatenate fields from multiple records-
DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link).