Access select rows with same ID but different values in 2 other columns

Question

I have following table in Access 2007:

Route   Cust    Planners
4401    1004    jasper
4401    1005    onno
4401    1006    jasper
4402    1007    bart
4402    1008    marcel
4402    1059    onno
4403    1124    bart
4403    1165    marcel
4403    1198    marcel
4403    1201    mustafa
4403    1225    bart
4401    1178    maurice

And I want this result (all the cust together and all the planners together that have same route):

Route   Cust                      Planners
4401    1004;1006;1178;1005       jasper;maurice;onno
4402    1007;1008;1059            bart;marcel;onno
4403    1124;1225;1165;1198;1201  mustafa;marcel;bart

Is this possible to do with a query or only via VBA-code? Thanks in advance!

EDIT @Dan (SOLVED): Thanks for the steps, I had the idea of doing something like this but didn't know exactly how. Below the code that worked for me (It can maybe be done easier and with less code but it does the job)

Dim rs As DAO.Recordset
Dim cust, planners As String
Dim route, route2, cntr As Integer

DoCmd.RunSQL "Delete * FROM details2"

Set rs = CurrentDb.OpenRecordset("select * from details order by route, cust")

rs.MoveFirst
cntr = 0
Do While Not rs.EOF
    route = rs.Fields("Route")
    'If route readed from recordset is not the same as local variable then insert that route and give local variable the new route and clear cust and planners
    If route <> route2 Then
        'If going through loop for first time => do no insert
        If cntr > 0 Then
            'Delete last comma for both cust and planners field and insert that route
            cust = Left(cust, Len(cust) - 1)
            planners = Left(planners, Len(planners) - 1)
            DoCmd.RunSQL "insert into details2 (Route, Cust, Planners) Values (" & route2 & ", '" & cust & "', '" & planners & "')"
        End If
        route2 = route
        cust = ""
        planners = ""
    End If

    'If route readed is the same as local variable, add cust and planner to the string but only if it isn't in the string already
    If route = route2 Then
        If InStr(cust, CStr(rs.Fields("cust"))) = 0 Then cust = cust + CStr(rs.Fields("cust")) & ","
        If InStr(planners, rs.Fields("planners")) = 0 Then planners = planners + rs.Fields("planners") & ","
    End If
    rs.MoveNext
    cntr = cntr + 1
Loop

'Delete last comma for cust and planners field and add last route
cust = Left(cust, Len(cust) - 1)
planners = Left(planners, Len(planners) - 1)
DoCmd.RunSQL "insert into details2 (Route, Cust, Planners) Values (" & route2 & ", '" & cust & "', '" & planners & "')"

And this gave me following results:

Route   Cust                        Planners
4401    1004,1005,1006,1178         jasper,onno,maurice
4402    1007,1008,1059              bart,marcel,onno
4403    1124,1165,1198,1201,1225    bart,marcel,mustafa

Thank you very much!

I doubt you really want lists of values separated by semicolons; can't you use a simple "SELECT Route, Cust, Planners FROM whatever ORDER BY Route", with all the data for each Route in consecutive rows, and then process one row at a time collecting individual Cust and Planners values into the proper data structures without cumbersome parsing? — Lorenzo Gatti, Apr 10 '14 at 07:34
@LorenzoGatti: What if the result is intended for report output? In that case, it makes perfect sense to concatenate strings over groups, to give the end-user a quick and compact overview over Cust's and Planner's for a given Route. — Dan, Apr 10 '14 at 07:43
@LorenzoGatti: Well, it doesn't have to be a semicolon, it can be a comma. But how can i process one row at a time in a query? — kvdw, Apr 10 '14 at 07:44
@Dan: Yes, exactly. It is just to have a quicker view which Customers and Planners there are for a certain Route — kvdw, Apr 10 '14 at 07:46

score 0 · Accepted Answer · edited May 23 '17 at 10:33

While Access SQL does not itself have any ways of performing string concatenation as an aggregate over groups, you can certainly achieve this using a combination of SQL and VBA.

I cannot give you the actual code, but the idea is along the following lines:

Create a new table to hold your output. Make sure your Cust and Planners columns are sufficiently long.
In VBA, create a variable to store the Route-number. Initialize it to 0 or some other value that does not appear in your database. Also create string variables for Cust and Planners. Initialize them to an empty string.
Open a recordset sorted by Route, and iterate through all records.
For each record, check if the value of the Route column is equal to the value stored in the Route-variable.
If the values are identical, add (concatenate) the values of the Cust and Planners columns to their respective variables.
If the Route column value is not identical to the Route variable value, insert a new record in your output table with the current values of your variables. Reset the Cust and Planners variable, and assign the values from the current record. Assign the new Route column value to your Route variable.
Continue the iteration.
After iteration is complete, perform one last insert into your output table. The output table should now hold the result you're looking for.

Thanks for this, I edited my post with the working code – kvdw Apr 10 '14 at 09:51 — kvdw, Apr 10 '14 at 09:51
Good job! Your code looks great. Glad to be of help :-) – Dan Apr 10 '14 at 10:00 — Dan, Apr 10 '14 at 10:00

score 0 · Answer 2 · answered Apr 10 '14 at 08:49

0

You have to create a VBA Function. Then use the below Query.

Here is the Source link

SELECT CompanyName,  ConcatRelated("Cust", "Table_name", "Route= " & [Route]) 
FROM Table_name;

answered Apr 10 '14 at 08:49

Prahalad Gaggar

11,389
16
53
71

Thanks for the help, this is somewhat similar to what Dan told me, but I just use a button that does the code because I find that easier for me – kvdw Apr 10 '14 at 09:54

Access select rows with same ID but different values in 2 other columns

2 Answers2