Solved

How to combine query results from multiple records in one field

Posted on 2009-05-11
5
208 Views
Last Modified: 2012-05-06
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
Comment
Question by:PopoDi
  • 3
  • 2
5 Comments
 

Author Comment

by:PopoDi
Comment Utility
I should explain that I need the one-line results above for the report.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
0
 

Author Comment

by:PopoDi
Comment Utility
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
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:PopoDi
Comment Utility
Thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Salary Amount Format 13 56
Need to fix 3 LEFT JOIN on same column 24 46
how to fix this error 14 45
sql calculate averages 18 24
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now