Query to Combine Records if one field is the same

[smoothlarryhughes]

I'm sure this is easy...but any help would be great.

If I have a table with these records:

a b c d e f
1 2 3 4 e f

If column 5 is equal combine to look like this:

a1 b2 c3 d4 e ff

Thanks!

 

[smoothlarryhughes]

Thanks for this: So I need to have my table named tbl_TEST and this should work? Do I need to rename my column headings to what you have in your results?

 

[gaj104]

Just realised my SQL wasn't quite right. It missed out a record. I've updated it to below:

SELECT T3.CASEID,
       T3.F_STATUS,
       T3.[F_AGING (DAYS)],
       T3.[F_SECURITY ID],
       T3.[F_SECURITY ID CUSIP],
       T3.F_BP2S,
       T3.F_CUSTODIAN
FROM   (SELECT   T2.CASEID,
                 First(T2.S_STATUS)              AS F_STATUS,
                 First(T2.[S_AGING (DAYS)])      AS [F_AGING (DAYS)],
                 First(T2.[S_SECURITY ID])       AS [F_SECURITY ID],
                 First(T2.[S_SECURITY ID CUSIP]) AS [F_SECURITY ID CUSIP],
                 First(T2.S_BP2S)                AS F_BP2S,
                 First(T2.S_CUSTODIAN)           AS F_CUSTODIAN
        FROM     (SELECT T1.CASEID,
                         Iif([TBL_TEST].[STATUS] = [TBL_TEST_1].[STATUS],
                             [TBL_TEST].[STATUS],[TBL_TEST].[STATUS] & [TBL_TEST_1].[STATUS]) AS S_STATUS,
                         Iif([TBL_TEST].[AGING (DAYS)] = [TBL_TEST_1].[AGING (DAYS)],
                             [TBL_TEST].[AGING (DAYS)],[TBL_TEST].[AGING (DAYS)] & [TBL_TEST_1].[AGING (DAYS)]) AS [S_AGING (DAYS)],
                         Iif([TBL_TEST].[SECURITY ID] = [TBL_TEST_1].[SECURITY ID],
                             [TBL_TEST].[SECURITY ID],[TBL_TEST].[SECURITY ID] & [TBL_TEST_1].[SECURITY ID]) AS [S_SECURITY ID],
                         Iif([TBL_TEST].[SECURITY ID CUSIP] = [TBL_TEST_1].[SECURITY ID CUSIP],
                             [TBL_TEST].[SECURITY ID CUSIP],[TBL_TEST].[SECURITY ID CUSIP] & [TBL_TEST_1].[SECURITY ID CUSIP]) AS [S_SECURITY ID CUSIP],
                         Iif([TBL_TEST].[BP2S] = [TBL_TEST_1].[BP2S],[TBL_TEST].[BP2S],
                             [TBL_TEST].[BP2S] + [TBL_TEST_1].[BP2S]) AS S_BP2S,
                         Iif([TBL_TEST].[CUSTODIAN] = [TBL_TEST_1].[CUSTODIAN],
                             [TBL_TEST].[CUSTODIAN],[TBL_TEST].[CUSTODIAN] + [TBL_TEST_1].[CUSTODIAN]) AS S_CUSTODIAN
                  FROM   TBL_TEST AS TBL_TEST_1
                         INNER JOIN (TBL_TEST
                                     INNER JOIN (SELECT   TBL_TEST.CASEID
                                                 FROM     TBL_TEST
                                                 GROUP BY TBL_TEST.CASEID
                                                 HAVING   (((Count(TBL_TEST.CASEID)) > 1))) AS T1
                                       ON TBL_TEST.CASEID = T1.CASEID)
                           ON TBL_TEST_1.CASEID = T1.CASEID
                  WHERE  ((Len(Iif([TBL_TEST].[SECURITY ID] = [TBL_TEST_1].[SECURITY ID],
                                   [TBL_TEST].[SECURITY ID],[TBL_TEST].[SECURITY ID] & [TBL_TEST_1].[SECURITY ID])) > Len([TBL_TEST].[SECURITY ID])))) AS T2
        GROUP BY T2.CASEID) AS T3
UNION ALL
SELECT T1.CASEID,
       TBL_TEST.STATUS,
       TBL_TEST.[AGING (DAYS)],
       TBL_TEST.[SECURITY ID],
       TBL_TEST.[SECURITY ID CUSIP],
       TBL_TEST.BP2S,
       TBL_TEST.CUSTODIAN
FROM   TBL_TEST
       INNER JOIN (SELECT   TBL_TEST.CASEID
                   FROM     TBL_TEST
                   GROUP BY TBL_TEST.CASEID
                   HAVING   (((Count(TBL_TEST.CASEID)) < 2))) AS T1
         ON TBL_TEST.CASEID = T1.CASEID

You dont have to rename your table, you can just do a find and replace on TBL_TEST to the name of your table. As for the columns they are the same as you gave. The F_ (final) and S_ (staging) are just alias to illustrate a staging and final result which I use in my SQL.

 

[smoothlarryhughes]

Thanks for this code! It works, but turns out there is a possibility there would be more than 2 case ID'd (very rare though)...can the code be tweaked to combine more than 2 rows if this is the case?

 

[gaj104]

Hi,

This SQL statement only works for 2 items. For any more duplicates a more efficient way would be to create a VBA function to give the output.

If you need a point in the right direction for the VBA feel free to reply back

Hope it helps

G

 

[wsmyth]

To: Graeme Jones

Thank you so much for this code. It worked like a champ in combining two like records with different values in a certain field, and concatenating those values while combining.

It was so fun to learn.

You might want to put a sticky (if possible) at the front of the thread. I entered the entire block of code on the first page, but then found out there was better code on page 2. Or edit the entry (or delete it if you can) so that others know better code is on page 2.

God Bless, and...
Cheers, Bill