Grouping records

[Robster]

Hi,
Hope you can help with this.

I have a table that has records with different values for the same person. What I need is to put those values into 1 field.
This is an example of the data.

Reference name item value
1234 Sam Smith ABT 12
1234 Sam Smith BTE 24
1234 Sam Smith CDE 13
1234 Sam Smith RGF 23
567 Joe Bloggs ABT 7
567 Joe Bloggs CDE 5
891 Fred Jones BTE 15
891 Fred Jones CDE 21
891 Fred Jones RGF 3

What I need is the following.
Reference name DATA
1234 Sam Smith ABT 12, BTE 24, CDE 13, RGF 23
567 Joe Bloggs ABT 7, CDE 5,
891 Fred Jones BTE 15, CDE 21, RGF 3

Please can someone let me know the best way to achieve this.

Thanks

 

[plog]

Check out Allen Brownes function:

http://allenbrowne.com/func-concat.html

 

[Robster]

Data in the office so will try in the morning.

 

[arnelgp]

here is a Modified version of
mr.Browne's concat function, since
you will be joining two fields.
copy in Standard Module.

Public Function fnConcatDATA(ByVal Reference As Variant, _
                             ByVal strFieldName As String, _
                             ByVal strTable As String, _
                             ParamArray FieldsToConcat() As Variant) As Variant
    
    Dim recSet As DAO.Recordset
    Dim varItem As Variant
    Dim strSQL As String
    
    strSQL = "SELECT [" & strFieldName & "] "
    Rem* loop through each fields to concatenate
    Rem* and built Query string
    For Each varItem In FieldsToConcat
        strSQL = strSQL & ", [" & varItem & "] "
    Next
    strSQL = strSQL & "FROM [" & strTable & "] "
    strSQL = strSQL & "WHERE [" & strFieldName & "]=" & FixSQL(Reference) & ";"
    Rem* open the recordset
    Set recSet = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    Rem* loop through each records in the recordset
    With recSet
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            For Each varItem In FieldsToConcat
                fnConcatDATA = fnConcatDATA & .Fields(varItem) & " "
            Next
            fnConcatDATA = Trim(fnConcatDATA) & ", "
            .MoveNext
        Wend
        .Close
        If Len(fnConcatDATA) > 0 Then fnConcatDATA = Left(fnConcatDATA, Len(fnConcatDATA) - 2)
    End With
    Set recSet = Nothing
End Function

Public Function FixSQL(p As Variant) As Variant
    Select Case VarType(p)
    Case VbVarType.vbNull
        FixSQL = "Null"
    Case VbVarType.vbString
        FixSQL = """" & p & """" 'Chr(34) & p & Chr(34)
    Case VbVarType.vbBoolean, VbVarType.vbByte, _
        VbVarType.vbCurrency, VbVarType.vbDecimal, _
        VbVarType.vbDouble, VbVarType.vbInteger, _
        VbVarType.vbLong, VbVarType.vbSingle, _
        FixSQL = p
    Case VbVarType.vbDate
        FixSQL = "#" & Format(p, "mm/dd/yyyy") & "#"
#If VBA7 Or Win64 Then
    Case VbVarType.vbLongLong
        FixSQL = p
#End If
    End Select
End Function

to call it, create a Total Query:


SELECT DATA.Reference, DATA.Name, fnconcatDATA([Reference],"Reference","DATA","Item","Value") AS DATA
FROM DATA
GROUP BY DATA.Reference, DATA.Name, fnconcatDATA([Reference],"Reference","DATA","Item","Value");


/just replace the DATA (tablename) with correct table name you have.

 

[Robster]

Didnt work. :banghead:
I create the VB Module and then created the query. I edited the SQL to match the correct table but when i try to run it, i get the error.
Run-time error '3075'
Syntax error (missing operator) in query expression '[AccountRef]='.
when I debug the below line is highlighted.
Set recSet = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Please help.

 

[Gasman]

So debug.print the strSQL string and see what is missing?

 

[Robster]

this is the code.
Dim recSet As DAO.Recordset
Dim varItem As Variant
Dim strSQL As String

strSQL = "SELECT [" & strFieldName & "] "
Rem* loop through each fields to concatenate
Rem* and built Query string
For Each varItem In FieldsToConcat
strSQL = strSQL & ", [" & varItem & "] "
Next
strSQL = strSQL & "FROM [" & strTable & "] "
strSQL = strSQL & "WHERE [" & strFieldName & "]=" & FixSQL(Reference) & ";"

 

[arnelgp]

Show us your Total Query. Are there null values on your fields?

 

[Gasman]

No, debug.print the actual SQL string.

Then you should be able to see what is wrong.

this is the code.
Dim recSet As DAO.Recordset
Dim varItem As Variant
Dim strSQL As String

strSQL = "SELECT [" & strFieldName & "] "
Rem* loop through each fields to concatenate
Rem* and built Query string
For Each varItem In FieldsToConcat
strSQL = strSQL & ", [" & varItem & "] "
Next
strSQL = strSQL & "FROM [" & strTable & "] "
strSQL = strSQL & "WHERE [" & strFieldName & "]=" & FixSQL(Reference) & ";"

 

[Robster]

this is the sql query.
SELECT SBCTask3.[AccountRef], SBCTask3.[Liable PArties], fnconcatDATA([AccountRef],"AccountRef","SBCTask3","Item","Value") AS SBCTask3
FROM SBCTask3
GROUP BY SBCTask3.[AccountRef], SBCTask3.[Liable PArties], fnconcatDATA([AccountRef],"AccountRef","SBCTask3","Item","Value");

 

[arnelgp]

do you have "Item" and "Value" fieldnames
in your table. if no, put the two
fieldname you need to join together.

 

[Gasman]

The code appears to be failing in the function, so debug that.
Sorry the code got all screwed up, but you should be able to get the picture.

Public Function fnConcatDATA(ByVal Reference As Variant, _                              ByVal strFieldName As String, _                              ByVal strTable As String, _                              ParamArray FieldsToConcat() As Variant) As Variant          Dim recSet As DAO.Recordset     Dim varItem As Variant     Dim strSQL As String          strSQL = "SELECT [" & strFieldName & "] "     Rem* loop through each fields to concatenate     Rem* and built Query string     For Each varItem In FieldsToConcat         strSQL = strSQL & ", [" & varItem & "] "     Next     strSQL = strSQL & "FROM [" & strTable & "] "     strSQL = strSQL & "WHERE [" & strFieldName & "]=" & FixSQL(Reference) & ";"     Rem* open the recordset
    [COLOR=Red]debug.print strSQL[/COLOR]
    Set recSet = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)     Rem* loop through each records in the recordset     With recSet         If Not (.BOF And .EOF) Then .MoveFirst         While Not .EOF             For Each varItem In FieldsToConcat                 fnConcatDATA = fnConcatDATA & .Fields(varItem) & " "             Next             fnConcatDATA = Trim(fnConcatDATA) & ", "             .MoveNext         Wend         .Close         If Len(fnConcatDATA) > 0 Then fnConcatDATA = Left(fnConcatDATA, Len(fnConcatDATA) - 2)     End With     Set recSet = Nothing End Function

 

[arnelgp]

if you can Upload your "real table"
with some dummy data, i want to
test it against that table.

 

[Robster]

Here is a test db that is trying to do what i need.

 

[arnelgp]

here is the fix, run your query.

 

[Robster]

Didnt work.
Have attached the error i get.

It says variable not defined.

 

[jdraw]

Try:

SELECT DISTINCT Tblname.Reference
, Tblname.name
, concatrelated("[item] & ' '  & [cost]","tblname","reference =" & [reference]) AS ConcatField
FROM Tblname
ORDER BY Reference desc;

Result of my test using Allen Browne's function

Reference	name	ConcatField
1234	Sam Smith	ABT 12 , BTE 24 , CDE 13 , RGF 23
891	Fred Jones	BTE 15 , CDE 21 , RGF 3
567	Joe Bloggs	ABT 7 , CDE 5

 

[arnelgp]

Remove the #If... Up to #End If

 

[Robster]

Finanlly got it working.... but it's killing the system so cant use.
I'm trying to replicate the following excel.
IF(A3=A2,S2&Q3,Q3))

 

[jdraw]

What exactly did you get working?

What does this mean--it's killing the system?