creating shipment confirmation

[shank]

I need to create a pipe delimited file with the following structure. I tried this in the past, gave up and now trying again. These are product orders.

Assuming there's only 1 line item - no problem:

Header row data includes PONo, InvoiceNo, OrderTotal, etc.
-LineItem1 row data contains Sku, Qty, PriceEach
-Shipment row includes Shipmethod, ShipDate, Tracking

My problem is when there's more than one line item. The delimited file still requires only one header row, but multiple LineItem and Shipment rows as follows.
This is correct:
Header row data includes PONo, InvoiceNo, OrderTotal, etc.
-LineItem1 row data contains Sku, Qty, PriceEach
-Shipment1 row includes Shipmethod, ShipDate, Tracking
-LineItem2 row data contains Sku, Qty, PriceEach
-Shipment2 row includes Shipmethod, ShipDate, Tracking


-LineItem3 row data contains Sku, Qty, PriceEach
-Shipment3 row includes Shipmethod, ShipDate, Tracking

My query is returning a header row for each LineItem which is invalid.
This is incorrect. It has 3 Header rows.
Header row data includes PONo, InvoiceNo, OrderTotal, etc.
-LineItem1 row data contains Sku, Qty, PriceEach
-Shipment1 row includes Shipmethod, ShipDate, Tracking
Header row data includes PONo, InvoiceNo, OrderTotal, etc.
-LineItem2 row data contains Sku, Qty, PriceEach
-Shipment2 row includes Shipmethod, ShipDate, Tracking
Header row data includes PONo, InvoiceNo, OrderTotal, etc.
-LineItem3 row data contains Sku, Qty, PriceEach
-Shipment3 row includes Shipmethod, ShipDate, Tracking

Each line requires a specific number of populated and blank fields so a union query doesn't seem to be the answer. I'm not seeing what is probably a simple solution. It's not like I could manually do this. There could be hundreds of orders. Any suggestions?

thanks!

[June7]

Exactly what do you want the text file to look like? Don't just describe it, provide an exact replica. You can attach a text document to post. Post an example of the source data.

[shank]

This is what it will look like. There are a lot of un-used fields. That's why so many | delimiters.

OS|CS|239101_005|239101_005|239101_005|20131220|ko hls|||||||||||||||||240.00|||200005||
OS|CD||1|93729345|CR30|5|v_ship|||48.00|||||||||||
OS|PD|UPSN_CG|20131220|1Z0000000000000000||||||||| ||||||||||||||||||||||||||||||
OS|CS|239053_003|239053_003|239053_003|20131220|ko hls|||||||||||||||||31.40|||200003||
OS|CD||5|93729468|M189|1|v_ship|||10.40|||||||||||
OS|PD|UPSN_CG|20131220|1Z0000000000000000||||||||| ||||||||||||||||||||||||||||||
OS|CD||3|93729478|M193|2|v_ship|||10.50|||||||||||
OS|PD|UPSN_CG|20131220|1Z0000000000000000||||||||| ||||||||||||||||||||||||||||||
OS|CS|239010_001|239010_001|239010_001|20131220|ko hls|||||||||||||||||119.70|||200001||
OS|CD||1|93729684|SP421|3|v_ship|||39.90|||||||||| |
OS|PD|UPSN_CG|20131220|1Z0000000000000000||||||||| ||||||||||||||||||||||||||||||

[June7]

Is Shipmethod, ShipDate, Tracking by line item or for the entire order?

So PONum 239053_003 has 2 line items?

Blank fields in a UNION is not a problem - it's ordering the records by PO number with multiple line items that could be insurmountable, although I am not convinced it is. Look at the attached sample db.

If UNION query isn't feasible, only option I see is a VBA procedure that opens a recordset of the data and loops through recordset to:

1. write records to a temp table and export the temp table

or

2. write lines to a textfile object opened in VBA

Bing: Access VBA write text file

http://zo-d.com/blog/archives/progra...ect-excel.html