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

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

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
ls21gce
Asked:
ls21gce
  • 4
  • 3
  • 2
  • +2
1 Solution
 
etrain01Commented:
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
 
Jokra_the_BarbarianCommented:
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
 
bonjour-autCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bonjour-autCommented:
just stop the horses, it need further modificatio for your purpose
0
 
bonjour-autCommented:
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
 
ls21gceAuthor Commented:
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
 
harfangCommented:
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
 
harfangCommented:
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
 
bonjour-autCommented:
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
 
ls21gceAuthor Commented:
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
 
harfangCommented:
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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