Concatenate text data in grouping query

[tekkyun]

I have data table like this:-
OrderNo OrderLine SerialNo
123 10 45
123 10 46
456 10 22

and I need query to concatenate serial nos to produce this result in grouping query:-
Order No Order Line SerialNos
123 10 45 46
456 10 22

I could write function to loop through records and string the serial nos together, but does anyone know an easier way in sql?
My data has up to 32 serial nos against each order/line.

 

[jjlogan]

Tek-
Why would you need to concatenate? Access is set up as relational database to avoid that.
Suggestion: Use your data table (Order NO, Order Line, Serial No) to hold all possible combinations of these three fields. Then use queries against that table for printing reports or forms. [You have a many-to-many relationship here and so the table may have to hold a lot of records for each unique combination - but computer memory is generally readily available and with that data structure it is much easier to query - e.g. to find a particular combination serial number-order].
Jeff

 

[lespaul]

check out the FAQ areas of the Access Forums, there are several postings on concatenating one to many relationships into a single field.

Jeff, there are reason why you would want to combine like data for reporting purposes.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886