Solved

Simple MS Access SQL query to group Values from consecutive rows into one field.

Posted on 2004-10-27
280 Views
Last Modified: 2013-06-11
Hi, this looks embarrassingly simple !

I have a basic MS Access table with the following info

Part    Reference   Value
1023   099            A
1023   099            N
1023   099            G
1055   017            F
1061   019            A
1061   019            S
1061   019            T
1061   019            Y
1099   025            B

I Need to Convert this Table with an SQL Query to:

Part   Reference   Value
1023   099         A,N,G
1055   017         F
1061   019         A,S,T,Y
1099   025         B

Anyone got a hint on how to easily achieve this ?

Thanks

SMc
0
Question by:ls21gce
    11 Comments
     
    LVL 5

    Expert Comment

    by:etrain01
    I don't know if you can directly do this with an sql in access.  Probably the best way would be to open a connection and then loop through all the records and query the new table for the reference and part and if it exists then add value to the new table value.  Otherwise insert the new record.

    Kind of hard to explain, but I hope you can understand...

    etrain01
    0
     
    LVL 11

    Expert Comment

    by:Jokra_the_Barbarian
    Well, this isn't embarrassingly simple. The grouping IS simple, but to get the value field you will have to call a function to create the string. So...
    1. Select t1.Part, t1.Reference, getValue(t1.Part, t1.Reference) as Value
        From
        ( Select Part, Reference
           From tblParts
           Group by Part, Reference
        ) as t1
        Order by t1.Part, t1.Reference

    2. Create a public function getValue to create the string:
       Public Function getValue(sPart as String, sRef as String) as String
         .....
           You will have to create a recordset based on the Part and Reference that you are looking up, grouping by value.
           Iterate through the recordset and create your value string, strVal = strVal & ", " & rst.Fields("Value")
         .....
       End Function

    0
     
    LVL 18

    Expert Comment

    by:bonjour-aut
    you need concatenation
    this can bee done by a public function
    or by subqueries in SQL

    example for concatenation:
    you will need to modify that

    Public Function recordrank(sqlsource As String, sqlfield As String, rankreference As Variant) As Integer
        Dim dbs As Database, recset As Recordset
        Dim refvalue, sqlstring As String
        refvalue = "[" & sqlfield & "] = " & Str$(rankreference)
        sqlstring = "SELECT " & sqlsource & "." & sqlfield & " FROM " & sqlsource & " ORDER BY " & sqlsource & "." & sqlfield & " ;"
        On Error Resume Next
        Set dbs = CurrentDb
        Set recset = dbs.OpenRecordset(sqlstring)
        recset.FindFirst refvalue
        recordrank = recset.AbsolutePosition + 1
        recset.Close
        Set dbs = Nothing
    End Function

    something like

    Public Function concatenate(sqlsource As String, sqlfield As String, myValue As Variant) As String
        Dim dbs As Database, recset As Recordset
        Dim refvalue, sqlstring, target As String
        refvalue = "[" & sqlfield & "] = " & Str$(rankreference)
        sqlstring = "SELECT " & sqlsource & "." & sqlfield & " FROM " & sqlsource & " WHERE((" & sqlsource & "." & sqlfield & " = " & sqlsource & "." & myValue  & "));"
        On Error Resume Next
        Set dbs = CurrentDb
        Set recset = dbs.OpenRecordset(sqlstring)
        recset.MoveFirst
        recset.MoveLast
        For i = 1 To recset.RecordCount-1
           target = target & ","
        Next i    
        recset.Close
        Set dbs = Nothing
        concatenate = Left(target,Len(target)-1) ' get rid of last ,
    End Function

    Regards, franz
    0
     
    LVL 18

    Expert Comment

    by:bonjour-aut
    just stop the horses, it need further modificatio for your purpose
    0
     
    LVL 18

    Expert Comment

    by:bonjour-aut
    Public Function concatenate(sqlsource As String, sqlfield As String, myKey As String, myValue As Variant) As String
        Dim dbs As Database, recset As Recordset
        Dim refvalue, sqlstring, target As String
        refvalue = "[" & sqlfield & "] = " & Str$(rankreference)
        sqlstring = "SELECT " & sqlsource & "." & sqlfield & " FROM " & sqlsource & " WHERE((" & sqlsource & "." & myKey & " = "  myValue  & "));"
        On Error Resume Next
        Set dbs = CurrentDb
        Set recset = dbs.OpenRecordset(sqlstring)
        recset.MoveFirst
        recset.MoveLast
        For i = 1 To recset.RecordCount-1
           target = target & ","
        Next i    
        recset.Close
        Set dbs = Nothing
        concatenate = Left(target,Len(target)-1) ' get rid of last ,
    End Function

    usage in a query e.g.

    select *, concatenate("myTable","Value","Part",myTable.Part) As myResult From myTable;

    Regards, Franz
    0
     

    Author Comment

    by:ls21gce
    Many thanks bonjour-aut.....

    ....that solution looks good to me. I am going to bed now, I will try it on my table tomorrow....

    SMc
    0
     
    LVL 58

    Accepted Solution

    by:
    To my knowlege, there is no way to achieve this using only JetEngine SQL...

    The solution above is sound for relatively small tables, and relatively unfrequent usage (e.g. reporting). For better performance, you will need to create a fast lookup function. Instead of opening an instance of CurrentDB, and running a query at each call, you could open the table directly, in order to use its indexes.

    But first, I couldn't determine from the data whether Part and Reference are distinct keys or a combined key. For the purpose of the sample below, let's assume that it's a combined key.

    Let's call your "basic table" tblBasic. Add in that table an index combining the fields Part and Reference, calling it "ndxPartReference". Once you have this, create the following function:

    Function PartRefValues(pvarPart, pvarRef)

        Static srecBasic as Recordset   ' handle of table tblBasic
        Dim varValues as Variant

        ' Open table-type recordset for tblBasic:
        If srecBasic Is Nothing Then
            set srecBasic = CurrentDB.OpenRecordset("tblBasic", dbOpenTable)
            srecBasic.Index = "ndxPartReference"
        End If

        ' build list of Values for the Part/Ref combination...
        varValues = Null
        With srecBasic
            ' find first value
            .Seek "=", pvarPart, pvarRef
            If Not .NoMatch Then
                ' look to the end of the table if needed
                Do Until .EOF
                    ' but stop if Part/Ref no longer match!
                    If pvarPart <> !Part Or pvarRef <> !Reference Then Exit Do
                    ' Build result string
                    varValues = varValues + "," & !Value
                    .MoveNext
                Loop
            End If
        End With

        PartRefValues = varValues

    End Function

    Now you can find the value list for a Part/Reference combination by calling that function. I think you probably have tables for those elsewhere. If not, you can crete a unique list with:

       SELECE DISTINCT Part, Reference FROM tblBasic;

    If this query is called qselPartRefList, you will get the required result with:

       SELECT *, PartRefValues(Part, Reference) FROM qselPartRefList;


    You will ask next how to have the list of values sorted alphabetically... To do this, change the index ndxPartReference to a three field index, including the Value as well. Change then the line:

            .Seek "=", pvarPart, pvarRef

    to:

            .Seek ">", pvarPart, pvarRef, Null


    You will then ask how to remove duplicates from the list of values.... that is left as en exercise :)


    Cheers!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Private comment for bonjour-aut:

    You obviously were "thinking aloud" when typing your code samples. I especially liked the part where:

        For i = 1 To recset.RecordCount-1
           target = target & ","
        Next i    

    This create a bunch of commas. For 5 returned recodes, you get ",,,," :)

    (sorry, couldn't resist... au fait, t'est français? dans ce cas: "bonjour!" :)

    Cheers!
    0
     
    LVL 18

    Expert Comment

    by:bonjour-aut
    yes harfang
    missed just the payload

    correct code should be:

       For i = 1 To recset.RecordCount-1
           target = target & recset.fields(0) & ","
        Next i

    in fact i had no exact matcjing code piece at hand so i altered another one, ths is always a trap for such mistakes.

    Regards, Franz
    0
     

    Author Comment

    by:ls21gce
    Hi Harfang,

    Your Solution worked exactly as I needed.

    Only two things that gave me some trouble.

    1 - The had to change "Recordset" to "DAO.Recordset" otherwise the Seek command complained.

    2 - My table was left open and the process couldn't run through twice, so I added a test on .EOF and then set srecBasic.Close and srecBasic = Nothing.

    Thanks very much !

    SMc
     
    0
     
    LVL 58

    Expert Comment

    by:harfang
    1) Yes, i'm still too much with Access 97 :)
    2) True, the table is left open and your's was a good solution

    Another trick I use to close an internal static variable in a function would be:

        If IsNull(pvarPart) Then
            if Not srecBasic Is Nothing Then srecBasic.Close
            Set srecBasic = Nothing
            Exit Function
        End If

    When I need to relase the table, I would then use:

        PartRefValues Null, Null


    Anyway, glad I could help!

    Cheers
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    875 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