Transformating Query Output

[mab9]

I have a simple table of cross references, built like:

ItemNo1 ItemNo2
12345 ABC1
32346 BBC2
53456 ZZZ1
53456 ZZZ2

I'm trying to figure out a way to query against this table to transform the output into the figure below due to the 1-many relationship between the records.

ItemNo1 ItemNo2
12345 ABC1
32346 BBC2
53456 ZZZ1,ZZZ2

Any ideas? I'm not quite sure where to start. Thanks!

 

[pbaldy]

Would this work for you?

http://www.mvps.org/access/modules/mdl0004.htm

 

[mab9]

That's perfect, thanks!