Convert rows of data to single row

[JW61]

I have data in the format of :

123 AAA 789 EEE 1
123 AAA 987 FFF 1
234 BBB 102 HIJ 2
234 BBB 203 LKJ 1
234 BBB 304 TTT 3
456 CCC 717 ASD 1
456 CCC 191 ZXC 2

What I is to convert the data to :

123 AAA 789 EEE 1 987 FFF 1
234 BBB 102 HIJ 2 203 LKJ 1 304 TTT 3
456 CCC 717 ASD 1 191 ZXC 2

One row for each unique value in the first column. Please do not suggest the FAQ “How to concatenate multiple child records into a single value”. I do not want to concatenate the values into one field.

Thanks for your help.

 

[lespaul]

well if you don't want to concatenate, what do you want....that's the only way I can think of other than looping through the recordset and creating a text file.

Leslie

Have you met Hardy Heron?

 

[dhookom]

I think you can do this using the FAQ on multi-value crosstab combined with some type of ranking query that assigns 1, 2, 3,... across each group of records.

Duane
Hook'D on Access
MS Access MVP