Cross Tab Scenario

[CPGDeveloper]

Hi All ~

I'm sure there's a way to do this but can't seem to figure it out...if I query results like this:

ID/STATE
1/PA
1/NY
2/CA
2/NY
2/IL

How do I get to this:

ID/STATE1/STATE2/STATE3
1/PA/NY
2/CA/NY/IL

If I had a 3rd column in the original data that identified STATE1 or STATE2 etc, I could do it with a simple cross-tab query, but not sure how to go about that...any help would be greatly appreciated. Thanks.

 

[Micron]

If you can use code, I should be able to figure it out, but if you can't then I'm at a loss.
If there is a query solution, I'd be amazed but not surprised since these puzzles as queries are not my strong point. However, one could loop over a recordset and concatenate the values for each group of ID's. This would require an inner loop on the states and an outer loop on the ID's. Let me/us know.

EDIT - I just remembered this - Microsoft Access tips: Concatenate values from related records
might be a bit complicated for what you show but it could be a simple copy/paste exercise for you.

 

[Micron]

If you can use code, I should be able to figure it out, but if you can't then I'm at a loss.

...what you don't say is what happens if there is more than 1 NY for example. CA/NY/IL is one thing if that means there is only one of each. It is a different beast altogether if you actually have
2/CA
2/NY
2/IL
2/NY

but want what you posted.

 

[Micron]

Well, this was a bit of a challenge to get everything in the right place, and it turns out that an inner loop isn't actually required. It's late so I'm going to post the code and hit the hay.

Sub ConcatStates()
Dim db As DAO.Database, rs As DAO.Recordset
Dim lngID As Long, lngID2 As Long
Dim strStates As String

Set db = CurrentDb
Set rs = db.OpenRecordset("table1")
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    lngID2 = rs.Fields("ID") 'set 2nd comparative id to ID value of rec#1. For first rec, they will be the same
    Do While Not rs.EOF
        lngID = rs.Fields("ID") 'set 1st comparitive id to ID value of rec#1.
            If lngID2 = lngID Then 'for 1st rec, obviously they will be the same value, so
                strStates = strStates & rs.Fields("State") & "," 'add 1st State value to string
            Else 'if ID's not the same, append a record
                strStates = Left(strStates, Len(strStates) - 1) 'remove the trailing , (-1)
                db.Execute "INSERT INTO Table2(ID,STATES) VALUES(" & lngID2 & ",'" & strStates & "')"
                lngID2 = rs.Fields("ID") 'set 2nd comparitive to the new ID
                strStates = rs.Fields("State") & "," 'concatenate the "," to the state for the new ID
            End If
        rs.MoveNext
    Loop
    'when at the last record, the current strString value needs to be appended
    db.Execute "INSERT INTO Table2(ID,STATES) VALUES(" & lngID & ",'" & strStates & "')"
End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

The output is

ID	STATES
1​	NY,PA
2​	CA,NY,IL,

 

[CPGDeveloper]

Thank You So Very Much for your help -- that code works beautifully. Wonderful.

 

[Micron]

You're welcome. Should still work if there is only 1 state for an ID but I didn't test that.

 

[CPGDeveloper]

It does work with just one state as well. Thanks Again!