Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-27
11
Medium Priority
?
283 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
Comment
Question by:ls21gce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 5

Expert Comment

by:etrain01
ID: 12427773
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
ID: 12427850
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
ID: 12427937
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 18

Expert Comment

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

Expert Comment

by:bonjour-aut
ID: 12428004
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
ID: 12428082
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:
harfang earned 400 total points
ID: 12429391
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
ID: 12429439
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
ID: 12430579
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
ID: 12436673
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
ID: 12441189
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 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