Group By and string

[bodylojohn]

Perhaps this is not the right description but I needed a tittle.

I have a query that consists out of multiple table.

The query contains 3 fields:
1. Course_ID
2. Part_ID
3. Page

I grouped the first 2 but here is the thing:
I want the contents of the page field to be displayed next to each other like this.

Query without grouping:

Course_ID Part_ID Page
1 | 2 | A
1 | 2 | B
1 | 2 | C
2 | 4 | S
2 | 2 | V

And it should be this:
Course_ID Part_ID Page
1 | 2 | A, B, C
2 | 4 | S
2 | 2 | V

How can I achieve this?

 

[Dennisk]

In order to turn rows into into columns you require a pivot table or XTab in Access terminology.

 

[KenHigg]

I was going to recommend something like post 7 in the following thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

 

[bodylojohn]

I was going to recommend something like post 7 in the following thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

This looks like what I need.

Can this be done in a query?

 

[bodylojohn]

This looks like what I need.

Can this be done in a query?

Is there a way to do this in sql?

 

[boblarson]

I was going to recommend something like post 7 in the following thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

Hey Ken - did you know you can post a link to a specific post in a thread? To post a link to that thread, go look for the number on the top right and right click on the link and select COPY SHORTCUT and then you can paste that one. For example, your post here would be:

http://www.access-programmers.co.uk/forums/showpost.php?p=453246&postcount=7

 

[bodylojohn]

I just cant get it to work.

Is there really no way to do this in sql?

 

[boblarson]

I just cant get it to work.

Is there really no way to do this in sql?

No, you can't do it in SQL. Think about it for a second. How would SQL which knows about individual records and can aggregate them all together, create output that is irregular (i.e. the first three records go together, the next two, the next four, a single record, etc.). It can't. You either get one, or all, but nothing in between. So, you need to do it with VBA.

 

[bodylojohn]

The example of ken works perfectly.
But not with my data (see 1st post)
when I run the code it shows only one record but there is nothing in the description field (where the data should be consolidated)

 

[bodylojohn]

No, you can't do it in SQL. Think about it for a second. How would SQL which knows about individual records and can aggregate them all together, create output that is irregular (i.e. the first three records go together, the next two, the next four, a single record, etc.). It can't. You either get one, or all, but nothing in between. So, you need to do it with VBA.

The example of ken works perfectly.
But not with my data (see 1st post)
when I run the code it shows only one record but there is nothing in the description field (where the data should be consolidated)

 

[bodylojohn]

This is what I found out so far:

Using the following code:

Option Compare Database
Option Explicit

Public Sub IllustratieVerantwoording()
    Dim cn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    
        
    rs1.Open "Query1", cn, adOpenDynamic, adLockOptimistic
    rs2.Open "TestOutput", cn, adOpenDynamic, adLockOptimistic
    
    rs1.MoveFirst
    rs2.AddNew
    rs2![Id] = rs1![Id]
    rs2![Omschrijving] = rs1![Omschrijving]
    rs1.MoveNext
       
        
        Do While Not rs1.EOF
        If rs1![Id] = rs2![Id] Then
            rs2![Omschrijving] = rs2![Omschrijving] & ", " & rs1![Omschrijving]
        Else
            rs2.AddNew
            rs2![Id] = rs1![Id]
            rs2![Omschrijving] = rs1![Omschrijving]
            
        End If
                
        rs1.MoveNext

        Loop
        
    rs1.Close
    
    MsgBox "Klaar!"
        
End Sub

When I trace the code while running I can see that the consolidation goes well.

I can see the result like this:

ID Omschrijving
1 e
2 w, r, e

But the TestOutput table only shows the first record.

1 e

What am i doing wrong?

 

[bodylojohn]

It works..

I added an update to the statement.

Now it looks like this and it works:

Option Compare Database
Option Explicit

Public Sub IllustratieVerantwoording()
    Dim cn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    
        
    rs1.Open "Query1", cn, adOpenDynamic, adLockOptimistic
    rs2.Open "TestOutput", cn, adOpenDynamic, adLockOptimistic
    
    rs1.MoveFirst
    rs2.AddNew
    rs2![Id] = rs1![Id]
    rs2![Omschrijving] = rs1![Omschrijving]
    rs1.MoveNext
       
        
        Do While Not rs1.EOF
        If rs1![Id] = rs2![Id] Then
            rs2![Omschrijving] = rs2![Omschrijving] & ", " & rs1![Omschrijving]
            rs2.Update
        Else
            rs2.AddNew
            rs2![Id] = rs1![Id]
            rs2![Omschrijving] = rs1![Omschrijving]
            rs2.Update
        End If
                
        rs1.MoveNext

        Loop
        
    rs1.Close
    
    MsgBox "Klaar!"
        
End Sub

Thanks for all the help guys.