subitems separated by comma

[ivow]

I've got a table of shipments, with multiple orders in each shipment. I've got shipments table and an order table.
I've created a query that displays items from the shipments table:

ShipID ShipDate ShipLocation
802 Nov 27 Winnipeg
803 Nov 30 Regina
804 Nov 1 Toronto

If I include the orders table I get this:

ShipID ShipDate ShipLocation OrderID
802 Nov 27 Winnipeg V323
802 Nov 27 Winnipeg V324
803 Nov 30 Regina V325
803 Nov 30 Regina V326
804 Nov 1 Toronto V327

But what I'd like to display is this:

ShipID ShipDate ShipLocation OrderID
802 Nov 27 Winnipeg V323,V324
803 Nov 30 Regina V325,V326
804 Nov 1 Toronto V327

I used SELECT DISTINCTROW but only gives me the first OrderID, how could I display all the order ID's?
I'm not SET on having them separated by commas.




Ivo

 

[Isadore]

Ivo: you need to do a horizontal fill of the Order ID. We posted some materials on this some time ago (horizontal filling of fields) and someone submitted an improvement over the code I introduced. What I did was travel between two tables, using a bookmark each way, and collecting the values and sticking them horizontally.

Try doing a search and see what you come up with. If you don't have any luck, I'll send you a routine that works for me; albeit there is a better way -- however, my routine, which I never replaced, has worked hundreds of times without the first error so its ok. Let me know if you're still working on this.