Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    4

    Unanswered: Combine several records into one record

    How can I combine several records in a table into one record?

    Suppose that I have a table like Table1 in the attached image.
    Then I want to combine all records with the same value for Key1 in one record.
    The result is shown in Table2 in the attached image.

    I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
    Alternatively I could accept to turn to VBA that could do it. Any good links about this?
    Attached Thumbnails Attached Thumbnails Tables.jpg  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im sure you can get where you want wih SQL only.
    Are the 2 or 3 codes consistent or do you need to find what those keys are. If so you may need to run a query to isolate what those key values are.
    Having identified the key values you can then use that as a feedstock into the main queries.
    Run you aggregation queries ther. You may ned to do the aggregation as 3 separate queries and bind them in a top level query
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2014
    Posts
    4
    No, the Key2 values are NOT consistent.
    They do not depend on each other, nor on Key1.
    The field names of Table2 do not matter.

    Quote Originally Posted by healdem View Post
    Im sure you can get where you want wih SQL only.
    Are the 2 or 3 codes consistent or do you need to find what those keys are. If so you may need to run a query to isolate what those key values are.
    Having identified the key values you can then use that as a feedstock into the main queries.
    Run you aggregation queries ther. You may ned to do the aggregation as 3 separate queries and bind them in a top level query

  4. #4
    Join Date
    Jan 2014
    Posts
    4
    The problem is that I need the data in Table2 'left-justified'. For instance you will find X2 in two different columns.
    You might ask why - and the answer is that Table2 should have the same structure as a table in an old Excel model (and this model is outside of my control!).

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Looking n more detail Im not sure I can do it in pure SQL, although a pivot view may

    OK I can get to the output you want using:-
    first off a query that identifies the unique/DISTINCT Key 1 value
    Code:
    SELECT DISTINCT (Key1) FROM mytable;
    I saved that and called the query DistinctKey1. the name you give that query isn't important EXCEPT you must use the same name in the second query (see below)

    then create a VBA function in a code module
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : GetNthKey
    ' Author    : healdem
    ' Date      : 24/01/2014
    ' Purpose   : retrieve the Nth row from a recordset
    'data is
    'Key1   Key2    Data
    'Key 1 repeats, but Key2 doesn't(so a Key1/Key2 pairing is unique)
    'we want the Nth Key 2 for the specified Key1
    '---------------------------------------------------------------------------------------
    '
    Public Function GetNthKey(FirstKey, Index) As String
        GetNthKey = "" 'declare our defualt retuirn value
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim iLoop As Integer
        'note the ORDER BY clause forces the sequence to be in ascending alphanumeric order
        Set rs = CurrentDb.OpenRecordset("Select Key2 from mytable where key1='" & FirstKey & "' ORDER BY Key2")
        iLoop = 1
        While rs.EOF = False 'iterate through the record set until
            If iLoop = Index Then 'either we found the nth value
                GetNthKey = rs!Key2 'OK so we found the nth value, store it in our return value
                Exit Function 'as we found the nth value we can quit the function
            Else 'or we keep going
              rs.MoveNext 'move to the next row
              iLoop = iLoop + 1 'increm,ent our counter so we know how many rows we've processed
            End If
            'note if we reached the end of the recordset before the nth value was found then we use our defautl value of ""
        Wend
    End Function
    then create a query which links the distinct Key1 with each Key2 pairing
    Code:
    SELECT DistinctKey1.Key1,
           getnthkey([DistinctKey1].[Key1],1) AS Key21, mytable_1.Data as Data1,
           getnthkey([DistinctKey1].[Key1],2) AS Key22, mytable_2.Data as Data2,
           getnthkey([DistinctKey1].[Key1],3) AS Key23, mytable_3.Data as Data3
    FROM ((DistinctKey1
    LEFT JOIN mytable AS mytable_1 ON DistinctKey1.Key1 = mytable_1.Key1 and mytable_1.key2=getnthkey([DistinctKey1].[Key1],1))
    LEFT JOIN mytable AS mytable_2 ON DistinctKey1.Key1 = mytable_2.Key1 and mytable_2.key2=getnthkey([DistinctKey1].[Key1],2))
    LEFT JOIN mytable AS mytable_3 ON DistinctKey1.Key1 = mytable_3.Key1 and mytable_3.key2=getnthkey([DistinctKey1].[Key1],3);
    As said before its quite possible you can get to the same solution using a pivot table view

    BTW if you need to add additional key pairings then add additional rows to the query

    Code:
    SELECT DistinctKey1.Key1,
           getnthkey([DistinctKey1].[Key1],1) AS Key21, mytable_1.Data as Data1,
           getnthkey([DistinctKey1].[Key1],2) AS Key22, mytable_2.Data as Data2,
           getnthkey([DistinctKey1].[Key1],3) AS Key23, mytable_3.Data as Data3
           getnthkey([DistinctKey1].[Key1],4) AS Key23, mytable_4.Data as Data4
    FROM (((DistinctKey1
    LEFT JOIN mytable AS mytable_1 ON DistinctKey1.Key1 = mytable_1.Key1 and mytable_1.key2=getnthkey([DistinctKey1].[Key1],1))
    LEFT JOIN mytable AS mytable_2 ON DistinctKey1.Key1 = mytable_2.Key1 and mytable_2.key2=getnthkey([DistinctKey1].[Key1],2))
    LEFT JOIN mytable AS mytable_3 ON DistinctKey1.Key1 = mytable_3.Key1 and mytable_3.key2=getnthkey([DistinctKey1].[Key1],3))
    LEFT JOIN mytable AS mytable_4 ON DistinctKey1.Key1 = mytable_4.Key1 and mytable_4.key2=getnthkey([DistinctKey1].[Key1],4);
    Last edited by healdem; 01-24-14 at 06:08.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2014
    Posts
    4
    Hi healdem

    Thank you, an elegant solution!

    I have attacked the problem in VBA (mainly because I do not meet your level of SQL expertise). The VBA is attached.
    So now I have two solutions:
    Your solution that has a minor drawback, since the number of field pairs in the output table is fixed by the SQL (3 or 4 in your example),
    and my solution that has a drawback since it is not a query but some VBA that for for instance must be activated by clicking a user defined button in the ribbon.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •