How to summarize the fields of the table this way?

**jamal numan** · 10-28-2011, 05:23 AM

How to summarize the fields of the table this way?

please, see the attached screen shot that show my question

i wanted to summarize the data according the values of Field1 and to get the values of Field1 and Field2 summarized separating the values with comma.

How can i do that?

thank you in advance,

regards

Jamal

**Robeen** · 10-28-2011, 08:02 AM

I have not been able to do this in a query.
I did find something on line one time that uses a Concatenate function but I was not able to adapt it to what I needed.
I ended up writing a VBA code function to read through each row of data and process it similar to what you want to do.
Do you want to search on line for how to Group on Field1 & Concatenate Fields 2 & 3?
If you don't find it . . . I can walk you through how to set the code up.
Let me know.

**jamal numan** · 10-28-2011, 09:46 AM

Originally Posted by Robeen

I have not been able to do this in a query.
I did find something on line one time that uses a Concatenate function but I was not able to adapt it to what I needed.
I ended up writing a VBA code function to read through each row of data and process it similar to what you want to do.
Do you want to search on line for how to Group on Field1 & Concatenate Fields 2 & 3?
If you don't find it . . . I can walk you through how to set the code up.
Let me know.

Thank you Robeen for the reply.

The problem that i don't have that much experience in writing codes!

IT would be very much appreciated if you indicate me where i can find such code that can be further generalized in case i have many Fields to be concatenated based on grouping Field1.

I'm wondering why Access doesn't have this command as built in tool for an end user like me!

thank you

regards

Jamal

**jamal numan** · 10-29-2011, 01:37 AM

Originally Posted by Robeen

I have not been able to do this in a query.
I did find something on line one time that uses a Concatenate function but I was not able to adapt it to what I needed.
I ended up writing a VBA code function to read through each row of data and process it similar to what you want to do.
Do you want to search on line for how to Group on Field1 & Concatenate Fields 2 & 3?
If you don't find it . . . I can walk you through how to set the code up.
Let me know.

Hi Robeen,

I would like to thank you first for the help that you offered yesterday.

If your time permits, I'm interested in this thread the most.

I'm attaching a typical example (table14, table15, table16, table17, table18)

where:

table14: is the source table (row data)
table15,16,17: the combo tables from which table14 reads its values
table18: the result table the i wish to reach!

It would be very much appreciated if you help me in building a code or module or macro that achieves this grouping and concatenating so that i can apply it in my actual data.

regards

Jamal

**Robeen** · 10-31-2011, 08:09 AM

I've opened your db.
Just to make sure I understand - you want the fields in Table14 - summarized into Table18 - like your screenshot - is that correct?

**jamal numan** · 10-31-2011, 09:52 AM

Originally Posted by Robeen

I've opened your db.
Just to make sure I understand - you want the fields in Table14 - summarized into Table18 - like your screenshot - is that correct?

Perfect! this is what I'm looking for: to summarize table14 in table18.

very much appropriated.

regards

Jamal

**Robeen** · 10-31-2011, 11:35 AM

Jamal,

Here is a function that will do what you need.

There is a problem, though.

When I tried to run this function using data from Table14 - instead of the visible values for Field2, Field3 & Field4 - I was getting only integer values [like 1, 2, 3, 4 . . .].

If you want to see the problem - try this:
Open Table14.
Press Ctrl + A - and then Ctrl + C [to copy all the data].
Open Table18
Click to the left of the Last [New - *] row of the Table - so that the row is selected/highlighted.
Click Ctrl + V to paste the data from Table14.
You should see what I am trying to describe.
??

I think this is because the values in Table14 are lookup values???
So when I run the function I am getting the lookup Value - rather than the text.

Honestly, I don't know because I have never used lookup fields in tables.

I am busy with work so I don't have the time to figure out how to overcome that
[

]

. . . so I created a Table named AA_Test - which has the same data as your Table14 [AA_Test has fields named One, Two, Three, Four].

I ran the function using data from AA_Test.

In the function, I wrote the summarized data to another empty table I created named AA_Test_Write [which has fields named One, Two, Three, Four].

Code:

 
 
Function Get_DB_Values()
'Get values from a table using a query in VBA.
'Process values row by row.
'Insert processed row into another Table.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim strField1, strField2, strField3, strField4 As String
Dim strNewField1, strNewField2, strNewField3, strNewField4 As String
Dim strSQL As String
Dim intRecordCount As Integer
 
On Error GoTo Error_Handle
 
Set db = CurrentDb
 
strSQL = "Select * From [AA_Test] ORDER  BY [One], [Two], [Three], [Four]"
intRecordCount = 1
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rs
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
If intRecordCount = 1 Then
    strField1 = rs![One]
    strField2 = rs![Two]
    strField3 = rs![Three]
    strField4 = rs![Four]
    intRecordCount = intRecordCount + 1
Else                                             'Not first record.
    strNewField1 = rs![One]
    If strNewField1 = strField1 Then    'Same Field1 - concatenate values.
        strNewField2 = rs![Two]
        strNewField3 = rs![Three]
        strNewField4 = rs![Four]
        strField2 = strField2 & ", " & strNewField2
        strField3 = strField3 & ", " & strNewField3
        strField4 = strField4 & ", " & strNewField4
 
    Else                                         'Field1 changed - Write the record to other table.
        'Create Insert SQL.
        strSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
        strSQL = strSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
 
        'Execute Insert SQL
        DoCmd.RunSQL strSQL
 
        'Populate current row values into variables.
        strField1 = rs![One]
        strField2 = rs![Two]
        strField3 = rs![Three]
        strField4 = rs![Four]
 
    End If                              'End If strNewField1 = strField1 Then
End If                                  'End If intRecordCount = 1
 
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
    'Create SQL for Last Row of data that is still stored even though Access found the EOF.
    strSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
    strSQL = strSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
    'Execute Insert SQL.
    DoCmd.RunSQL strSQL
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

I hope this solves your problem or at least that you can use this as a starting point.

Let me know if there are problems with this!!
All the best!

**jamal numan** · 10-31-2011, 12:16 PM

Originally Posted by Robeen

Jamal,

Here is a function that will do what you need.

There is a problem, though.

When I tried to run this function using data from Table14 - instead of the visible values for Field2, Field3 & Field4 - I was getting only integer values [like 1, 2, 3, 4 . . .].

If you want to see the problem - try this:
Open Table14.
Press Ctrl + A - and then Ctrl + C [to copy all the data].
Open Table18
Click to the left of the Last [New - *] row of the Table - so that the row is selected/highlighted.
Click Ctrl + V to paste the data from Table14.
You should see what I am trying to describe.
??

I think this is because the values in Table14 are lookup values???
So when I run the function I am getting the lookup Value - rather than the text.

Honestly, I don't know because I have never used lookup fields in tables.

I am busy with work so I don't have the time to figure out how to overcome that
[

]

. . . so I created a Table named AA_Test - which has the same data as your Table14 [AA_Test has fields named One, Two, Three, Four].

I ran the function using data from AA_Test.

In the function, I wrote the summarized data to another empty table I created named AA_Test_Write [which has fields named One, Two, Three, Four].

Code:

 
 
Function Get_DB_Values()
'Get values from a table using a query in VBA.
'Process values row by row.
'Insert processed row into another Table.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim strField1, strField2, strField3, strField4 As String
Dim strNewField1, strNewField2, strNewField3, strNewField4 As String
Dim strSQL As String
Dim intRecordCount As Integer
 
On Error GoTo Error_Handle
 
Set db = CurrentDb
 
strSQL = "Select * From [AA_Test] ORDER  BY [One], [Two], [Three], [Four]"
intRecordCount = 1
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rs
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
If intRecordCount = 1 Then
    strField1 = rs![One]
    strField2 = rs![Two]
    strField3 = rs![Three]
    strField4 = rs![Four]
    intRecordCount = intRecordCount + 1
Else                                             'Not first record.
    strNewField1 = rs![One]
    If strNewField1 = strField1 Then    'Same Field1 - concatenate values.
        strNewField2 = rs![Two]
        strNewField3 = rs![Three]
        strNewField4 = rs![Four]
        strField2 = strField2 & ", " & strNewField2
        strField3 = strField3 & ", " & strNewField3
        strField4 = strField4 & ", " & strNewField4
 
    Else                                         'Field1 changed - Write the record to other table.
        'Create Insert SQL.
        strSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
        strSQL = strSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
 
        'Execute Insert SQL
        DoCmd.RunSQL strSQL
 
        'Populate current row values into variables.
        strField1 = rs![One]
        strField2 = rs![Two]
        strField3 = rs![Three]
        strField4 = rs![Four]
 
    End If                              'End If strNewField1 = strField1 Then
End If                                  'End If intRecordCount = 1
 
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
    'Create SQL for Last Row of data that is still stored even though Access found the EOF.
    strSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
    strSQL = strSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
    'Execute Insert SQL.
    DoCmd.RunSQL strSQL
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

I hope this solves your problem or at least that you can use this as a starting point.

Let me know if there are problems with this!!
All the best!

Hi Robeen,

Sounds you have exerted huge effort to accomplish grouping and concatenating.

May you please send me the file that contains the table AA_Test integrated with the module that you have created. my e-mail is: Jamal9722060@yahoo.com.

To be honest, i couldn't know how to integrate the module into my file (M4.accdb) and how to run it. I've very little experience in writing codes and modules.

The further problem is that i need to generalize the grouping and concatenating to include more than 12 fields!!!

very much appreciated

Regards

Jamal

**jamal numan** · 10-31-2011, 01:23 PM

Originally Posted by Robeen

Jamal,

Here is a function that will do what you need.

There is a problem, though.

When I tried to run this function using data from Table14 - instead of the visible values for Field2, Field3 & Field4 - I was getting only integer values [like 1, 2, 3, 4 . . .].

If you want to see the problem - try this:
Open Table14.
Press Ctrl + A - and then Ctrl + C [to copy all the data].
Open Table18
Click to the left of the Last [New - *] row of the Table - so that the row is selected/highlighted.
Click Ctrl + V to paste the data from Table14.
You should see what I am trying to describe.
??

I think this is because the values in Table14 are lookup values???
So when I run the function I am getting the lookup Value - rather than the text.

Honestly, I don't know because I have never used lookup fields in tables.

I am busy with work so I don't have the time to figure out how to overcome that
[

]

. . . so I created a Table named AA_Test - which has the same data as your Table14 [AA_Test has fields named One, Two, Three, Four].

I ran the function using data from AA_Test.

In the function, I wrote the summarized data to another empty table I created named AA_Test_Write [which has fields named One, Two, Three, Four].

Code:

 
 
Function Get_DB_Values()
'Get values from a table using a query in VBA.
'Process values row by row.
'Insert processed row into another Table.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim strField1, strField2, strField3, strField4 As String
Dim strNewField1, strNewField2, strNewField3, strNewField4 As String
Dim strSQL As String
Dim intRecordCount As Integer
 
On Error GoTo Error_Handle
 
Set db = CurrentDb
 
strSQL = "Select * From [AA_Test] ORDER  BY [One], [Two], [Three], [Four]"
intRecordCount = 1
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rs
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
If intRecordCount = 1 Then
    strField1 = rs![One]
    strField2 = rs![Two]
    strField3 = rs![Three]
    strField4 = rs![Four]
    intRecordCount = intRecordCount + 1
Else                                             'Not first record.
    strNewField1 = rs![One]
    If strNewField1 = strField1 Then    'Same Field1 - concatenate values.
        strNewField2 = rs![Two]
        strNewField3 = rs![Three]
        strNewField4 = rs![Four]
        strField2 = strField2 & ", " & strNewField2
        strField3 = strField3 & ", " & strNewField3
        strField4 = strField4 & ", " & strNewField4
 
    Else                                         'Field1 changed - Write the record to other table.
        'Create Insert SQL.
        strSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
        strSQL = strSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
 
        'Execute Insert SQL
        DoCmd.RunSQL strSQL
 
        'Populate current row values into variables.
        strField1 = rs![One]
        strField2 = rs![Two]
        strField3 = rs![Three]
        strField4 = rs![Four]
 
    End If                              'End If strNewField1 = strField1 Then
End If                                  'End If intRecordCount = 1
 
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
    'Create SQL for Last Row of data that is still stored even though Access found the EOF.
    strSQL = "INSERT INTO AA_Test_Write (One, Two, Three, Four) "
    strSQL = strSQL & "VALUES (" & "'" & strField1 & "'" & ", " & "'" & strField2 & "'" & ", " & "'" & strField3 & "'" & ", " & "'" & strField4 & "'" & "); "
    'Execute Insert SQL.
    DoCmd.RunSQL strSQL
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

I hope this solves your problem or at least that you can use this as a starting point.

Let me know if there are problems with this!!
All the best!

Hi again Robeen,

I followed your instructions:
---------------------------------------------------
If you want to run the Function:
Right-click ‘Module1’ & then click Design.
Select ‘Function Get_DB_Values()’.
On top of the screen Click the Green > under the ‘Run’ menu item.

The Function will write the summarized data to AA_Test_Write
--------------------------------------------------------------

It sounds that the code doesn't achieve exactly what I'm looking for! i don't need the values to be repeated in the concatenated fields!

please, have a look on the screenshot for the result that i expect and the result from the code that you have created.

what do you think?

I'm sure that you have other work to do but please consider this issue when your time allows

regards

Jamal

**Robeen** · 10-31-2011, 01:28 PM

Yes . . . I see it now.

Let me think about it - I will get back to you as soon as I get a chance - if no one else has solved it for you in the meantime.

**jamal numan** · 11-02-2011, 01:20 PM

Originally Posted by Robeen

Yes . . . I see it now.

Let me think about it - I will get back to you as soon as I get a chance - if no one else has solved it for you in the meantime.

For an end-user like me, there should be a built-in command in the query to do the concatenation!

Thanks Robeen.

regards

Jamal

**jamal numan** · 12-16-2011, 08:42 AM

Originally Posted by Robeen

Yes . . . I see it now.

Let me think about it - I will get back to you as soon as I get a chance - if no one else has solved it for you in the meantime.

Hi Robeen,

I could guess that you have been quite busy! i didn't get any response from the community in how such grouping can be constructed!

I'm not sure if your time allows you to figure it out!

thank you in advance,

regards

Jamal

**Robeen** · 12-16-2011, 08:53 AM

Sorry, Jamal. I will try & spend some time on this today.

**Robeen** · 12-16-2011, 02:28 PM

The solution is a little tricky - but you should try it if you want to improve in your VBA.

Each time there is a new row - you will get the values for all the fields into your variables.

1. Check if Field1 is the same as the previous row's Field1.
2. If it is - check if Field2, 3 & 4 are the same.
3. If any of 2, 3 or 4 is different - add it to the variable that you will be writing to the new row in your other table.
4. When Field1 changes - write Field1 & the stored values into a new row in the other table.

Because it is tricky, it takes time - and it is difficult for me to make time to do this as I am at work.

I will keep trying when I get the time.

**June7** · 02-13-2012, 08:34 PM

Review this Allen Browne tutorial http://allenbrowne.com/func-concat.html

How to summarize the fields of the table this way?

Thread Tools

Search Thread

How to summarize the fields of the table this way?

Similar Threads

Query : Matching 2 Fields from Table 1 to 2 fields from Table 2

Update query to summarize multiple values

Summarize on different columns and substract

VBA to update matching fields from one table and set unmatching fields to null

Table fields which depend on other fields

Posting Permissions