one to many

[ginger]

Hello

I have a table with ClientIDs and their diagnosis codes. ClientID has multiple records and each record has a different dx code.

ClientID DxCode
1111 F12.467
1111 F3.90
2222 F21.9
2222 F9.00
2222 F12.270

My goal is to have a table/datasheet that has ClientID appear once and string out the dx codes.

ClientID DIAG1 DIAG2 DIAG3 DIAG4 DIAG5
1111 F12.467 F12.467
2222 F21.9 F9.00 F12.270

I don't write much code and do mostly everything through querying but any help is much appreciated.

 

[jdraw]

Research Allen Browne's func-concat

 

[ginger]

Will do. Thanks!

 

[plog]

First, why? And second, your data sample doesn't match your explanation:

My goal is to have a table/datasheet that has ClientID appear once and string out the dx codes.

Allen Browne's code will do that for you (1 field for ClientId and 1 field for all Dx codes put together), but it will not provide the data like the sample you included. You listed 6 fields (ClientID and 5 Diag fields). Further, your initial data doesn't match your finishing data: ClientID only has one Dx of F12.467 not 2 as the resulting data shows.

Again, though, why? Why must you have your data like this?

 

[ginger]

The first why is because the person requesting the information wants it that way according to their specs. I will actually be sending it to them as an Excel spreadsheet.

The 2nd row for clientid 1111, diag2 should have been listed as F3.90. A cut and paste error.