• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

How to combine query results from multiple records in one field

I have two tables that I query, a LINE and a RUN table, related by keytags and I can have multiple RUN records for each LINE. My current query returns results like this:
LINE.name1, RUN.name1, RUN.ins1
LINE.name1, RUN.name2, RUN.ins2
LINE.name1, RUN.name3, RUN.ins3
LINE.name2, RUN.name1, RUN.ins1

I would like to get the results in this form though (one line per LINE record):
LINE.name1, [RUN.ins1 & RUN.ins2 & RUN.ins3]
LINE.name2, RUN.ins1

What do I have to add to my query to get this (which is currently just a simple select on the left join between LINE and RUN tables)?
Thanks!
0
PopoDi
Asked:
PopoDi
  • 3
  • 2
1 Solution
 
PopoDiAuthor Commented:
I should explain that I need the one-line results above for the report.
0
 
PopoDiAuthor Commented:
Hi ralmada,

I found fConcatFld function that seems to fit what I need more than the solution you linked to (I'm beginner in this so my judgement may be wrong), however I do have one problem with it. I'd like to return "none" if my ins field is null  (I cannot write it in the database because it is linked to another program and needs to stay null) and it seems that in some cases fConcatFld returns extra results. Most of them are correct, but in one case I have this:
Line Name      Run Ins
1402               null; null; 2hg; 2hg

and the fConcatFld return this:
Line Name      Run Ins
1402               2hg; 2hg; 2hg; 2hg; 2hg; ;

Included is the code for fConcatFld that I am using. Maybe I should repost this as a separate question.
'************ Code Start *********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'
Dim lodb As DAO.Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"
 
    On Error GoTo Err_fConcatFld
    
    lovConcat = Null
    Set lodb = CurrentDb
    
    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
    
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatFld
    End Select
    
    loSQL = loSQL & " ORDER BY [" & stFldToConcat & "]"
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
    
    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
        .MoveFirst
            'start concatenating records
            Do While Not .EOF
                lovConcat = lovConcat & lors(stFldToConcat) & "; "
                .MoveNext
            Loop
        Else
            GoTo Exit_fConcatFld
        End If
    End With
        
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
 
 
Exit_fConcatFld:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function
 
Err_fConcatFld:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatFld
End Function
'************ Code End **********

Open in new window

0
 
ralmadaCommented:
Hi,
I've modified your function a bit. Let me know if this is what you're looking for.

'************ Code Start *********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'
Dim lodb As DAO.Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"
 
    On Error GoTo Err_fConcatFld
    
    lovConcat = Null
    Set lodb = CurrentDb
    
    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
    
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatFld
    End Select
    
    loSQL = loSQL & " ORDER BY [" & stFldToConcat & "]"
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
    
    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
        .MoveFirst
            'start concatenating records
            Do While Not .EOF
                If IsNull(lors(stFldToConcat)) Then
                    lovConcat = lovConcat & "none" & "; "
                Else
                    lovConcat = lovConcat & lors(stFldToConcat) & "; "
                End If
                .MoveNext
            Loop
        Else
            GoTo Exit_fConcatFld
        End If
    End With
        
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
 
 
Exit_fConcatFld:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function
 
Err_fConcatFld:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatFld
End Function
'************ Code End **********

Open in new window

0
 
PopoDiAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now