Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2006
    Posts
    28

    Unanswered: Concatenate records

    I have absolutly no experience of using VB and so I am tying myself in knots! any help greatly appreciated!

    Basically I have a table:
    job no orderline heat number
    AM26 1 I60
    AM26 1 H27
    AM26 2 E360

    etc, but I want:
    job no orderline heat number
    AM26 1 I60, H27
    AM26 2 E360

    I have found some VB codes to concatenate, but have absolutly no clue how to use them! The most IO understand is that I have to paste the code into a module, change the field names (but not sure to what to!) and then to call the concatenate function in a query (also not sure how to do this!)

    please help me!

  2. #2
    Join Date
    Sep 2006
    Posts
    28
    I have a module "concatenate" which compiles ok. How do I use concatenate in my query????
    Function Concatenate(pstrSQL As String, _
    Optional pstrDelim As String = ", ") _
    As String
    'Created by Duane Hookom, 2003
    'this code may be included in any application/mdb providing
    ' this statement is left intact
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    ' John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    ' WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '

    '======For DAO uncomment next 4 lines=======
    '====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
    If Not .EOF Then
    .MoveFirst
    Do While Not .EOF
    strConcat = strConcat & _
    .Fields(0) & pstrDelim
    .MoveNext
    Loop
    End If
    .CLOSE
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
    strConcat = Left(strConcat, _
    Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
    End Function

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Create a query like this but replace tblFamMem with your table name, FirstName with the field name of the field you want to concatenate and "& [FamID])" with "& [YourPrimaryKeyColumn])".

    Code:
     
    SELECT FamID,
    Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID]) as FirstNames
    FROM tblFamily
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2006
    Posts
    28
    The SQL of my query is

    SELECT [tb:heat_numbers].[Barcode number], Concatenate("SELECT Barcode number FROM tb:heat_numbers WHERE DE_HEAT_NUM =" & [DE_HEAT_NUM]) AS [Heat numbers]
    FROM [tb:heat_numbers];


    runtime error -214721700 (8040e14)':
    syntax error (missing operator) in query expression 'Barcode number = 2200601'

  5. #5
    Join Date
    Sep 2006
    Posts
    28
    I now have the SQL

    SELECT [tb:Heat].[Barcode number], Concatenate("SELECT [DE_HEAT_NUM] FROM tb:Heat WHERE [Barcode number] =" & [Barcode number]) AS DE_HEAT_NUM, [tb:Heat].[DE_HEAT_NUM]
    FROM [tb:Heat];

    and the resulting table has #NAME? in column DE_HEAT_NUM and the records are not concatenated?!

    I'm definately a step closer though, thanks for your help!

  6. #6
    Join Date
    Sep 2006
    Posts
    28
    I've changed the SQL to

    SELECT Concatenate("SELECT [DE_HEAT_NUM] FROM tb:Heat_numbers WHERE [Barcode number] =" & [Barcode number]) AS DE_HEAT_NUM, [tb:heat_numbers].DE_HEAT_NUM, [tb:heat_numbers].[Barcode number]
    FROM [tb:heat_numbers];

    and the #NAME? has disappeared but the column is now blank and the heat numbers are still not concatenated!

    Any more clues?!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Always avoid special chars in your object names.

    Code:
    SELECT Concatenate("SELECT [DE_HEAT_NUM] FROM [tb:Heat_numbers] WHERE [Barcode number] =" & [Barcode number]) AS DE_HEAT_NUM, [tb:heat_numbers].DE_HEAT_NUM, [tb:heat_numbers].[Barcode number]
    FROM [tb:heat_numbers]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2006
    Posts
    28
    Thanks for that! I think it was the space between barcode and number that was doing it! all working now though!

    Thanks again

  9. #9
    Join Date
    Sep 2006
    Posts
    28
    Is there any way to concatenate records when the primary key is a date field?

    I have

    SELECT Concatenate("SELECT [Barcode_number] FROM tbljeffinspection WHERE [final] =" & [final]) AS CONC, tbljeffinspection.final
    FROM tbljeffinspection
    GROUP BY Concatenate("SELECT [Barcode_number] FROM tbljeffinspection WHERE [final] =" & [final]), tbljeffinspection.final;
    which only works if I have final as "number" - I need it to be date!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by clairepop1984
    Is there any way to concatenate records when the primary key is a date field?
    Code:
     
    SELECT DISTINCT Concatenate("SELECT [Barcode_number] FROM tbljeffinspection WHERE [final] = #" & [final] & "#") AS CONC, tbljeffinspection.final
    FROM tbljeffinspection
    No need for group by. In case you find it easier to use I prefer concat functions more like this guys:
    http://www.mvps.org/access/modules/mdl0008.htm
    The SQL is generated in the function which keeps your SQL tidier. Much of a muchness really though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2006
    Posts
    28
    excellent that works perfectly! Thankyou!
    One final thing! Is there anyway to choose the primary key from multiple fields?
    for example

    SELECT DISTINCT Concatenate("SELECT [Barcode_number] FROM tbljeffinspection WHERE ([final] = #" & [final] & "# or [f2] =# "&[f2] & "#)" AS CONC, tbljeffinspection.final or tbljeffinspection.f2
    FROM tbljeffinspection)

    thanks

  12. #12
    Join Date
    Sep 2006
    Posts
    28
    Its ok I worked it out! trial and error!

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by clairepop1984
    Its ok I worked it out! trial and error!
    Fancy posting the solution for posterity?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •