Results 1 to 6 of 6
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Asemble Header and Detail rows in text file

    Win 7, MS Access 2007. I need to assemble a header and detail row layout into a text file for a PO Acknowledgement. The one common field between the HEADER and DETAIL rows is the [PONo]. I'm using the LineNo field for sorting.

    Code:
    SELECT TempPOACKHeader.PONo, TempPOACKDetail.LineNo, TempPOACKHeader.HeaderData, TempPOACKDetail.DetailData
    FROM TempPOACKHeader INNER JOIN TempPOACKDetail ON TempPOACKHeader.PONo = TempPOACKDetail.PONo
    ORDER BY TempPOACKHeader.PONo, TempPOACKDetail.LineNo;
    The resulting data is below. PO12345 has 3 skus and PO67890 has 2 skus.
    In the end, I need a layout like shown below under PO Acknowledgement Data in a text file.
    Of course, in production this could be hundreds of records.
    Although foreign to me, I have to assume there are others that have been through this.
    I don't have a clue how to get the results I need. Any thoughts would be appreciated.

    Thanks!
    HeaderData
    OA|AH|||PO12345|444444|20190410|Acme Rockets|
    OA|AH|||PO12345|444444|20190410|Acme Rockets|
    OA|AH|||PO12345|444444|20190410|Acme Rockets|


    OA|AH|||PO67890|555555|20190410|Acme Rockets|
    OA|AH|||PO67890|555555|20190410|Acme Rockets|

    DetailData
    OA|AD||1|MerchSku1A|VendorSku1|111111111111|2||v_a ccept||2|
    OA|AD||2|MerchSku2A|VendorSku2|111111111112|4||v_a ccept||4|
    OA|AD||5|MerchSku3A|VendorSku3|111111111113|9||v_a ccept||9|
    OA|AD||2|MerchSku8B|VendorSku8|111111111115|1||v_a ccept||1|
    OA|AD||4|MerchSku7B|VendorSku7|111111111114|2||v_a ccept||2|

    PO Acknowledgement Data
    OA|AH|||PO12345|444444|20190410|Acme Rockets|
    OA|AD||1|MerchSku1A|VendorSku1|111111111111|2||v_a ccept||2|
    OA|AD||2|MerchSku2A|VendorSku2|111111111112|4||v_a ccept||4|
    OA|AD||5|MerchSku3A|VendorSku3|111111111113|9||v_a ccept||9|
    OA|AH|||PO67890|555555|20190410|Acme Rockets|
    OA|AD||2|MerchSku8B|VendorSku8|111111111115|1||v_a ccept||1|
    OA|AD||4|MerchSku7B|VendorSku7|111111111114|2||v_a ccept||2|

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So the above is what you want (the end result).
    What does the data from the query look like?
    Does the query you posted provide all of the data you want to write to the text file?


    Creating a text file (either .txt or .csv) is easy - there are a couple of ways. Because you want a specific format, there will be a lot of processing (VBA).

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Click image for larger version. 

Name:	S1.jpg 
Views:	9 
Size:	62.0 KB 
ID:	38055

    I inserted a screenshot above of the query. Didn't see a way to attach it. Yes, all the data is shown for 2 sample PO's. In production there could be hundreds of PO's. What I was envisioning was assembling the expected results into one field of one row, then exporting to a text file with the Saved Export function. I have no clue as to how to get this done.

    thanks!

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When you are composing a post, look at the bottom right for a button named "Go Advanced". Click it. Then scroll down to the button that is "Manage Attachments"... follow the prompts.
    You will be able to attach images or databases - providing the object does not exceed size limits.

  5. #5
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I did find a solution, but it's not pretty. First, I created a UNION query combining the header and detail data sorting the data by PONo and LineNo.

    Code:
    SELECT TempPOACKHeader.PONo, "0" AS LineNo, [TempPOACKHeader]![HeaderData] AS ACKData
    FROM TempPOACKHeader
    ORDER BY TempPOACKHeader.PONo, "0"
    UNION ALL SELECT TempPOACKDetail.PONo, TempPOACKDetail.LineNo, [TempPOACKDetail]![DetailData] AS ACKData
    FROM TempPOACKDetail
    ORDER BY TempPOACKHeader.PONo, LineNo;
    Next, create a query including only the [ACKData] field in the union query

    Code:
    SELECT qry_ACKData_UNION.ACKData
    FROM qry_ACKData_UNION
    ORDER BY qry_ACKData_UNION.PONo, qry_ACKData_UNION.LineNo;
    Then exporting with the SavedExport function.

    It works, but I have another question. I had to create the last query because I couldn't figure out how to sort the union query on [PONo] and [LineNo] fields when they are hidden. I kept getting the error: The order by expression includes fields that are not selected by the query etc. If anyone has a solution to that it would save me having to use the last query.

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think you are getting an error because there is not a field named "0" in the ORDER BY in the first SELECT of the UNION query:
    Code:
    SELECT TempPOACKHeader.PONo, "0" AS LineNo, [TempPOACKHeader]![HeaderData] AS ACKData
    FROM TempPOACKHeader
    ORDER BY TempPOACKHeader.PONo, "0"
    UNION ALL SELECT TempPOACKDetail.PONo, TempPOACKDetail.LineNo, [TempPOACKDetail]![DetailData] AS ACKData
    FROM TempPOACKDetail
    ORDER BY TempPOACKHeader.PONo, LineNo;
    Also, in the Union query, the second SELECT has the wrong domain in the ORDER BY clause


    Try this:
    Code:
    SELECT TempPOACKHeader.PONo, 0 AS LineNo, [TempPOACKHeader]![HeaderData] AS ACKData
    FROM TempPOACKHeader
    ORDER BY TempPOACKHeader.PONo, LineNo   '<<-- changed from "0"
    UNION ALL 
    SELECT TempPOACKDetail.PONo, TempPOACKDetail.LineNo, [TempPOACKDetail]![DetailData] AS ACKData
    FROM TempPOACKDetail
    ORDER BY TempPOACKDetail.PONo, LineNo;   '<<-- changed from TempPOACKHeader


    BTW, putting quotes around a number makes the number text. "0" is not the same as 0!
    I cannot determine, from what you have posted, if the field "LineNo" is a number type or text type (in the table).





    Great job coming up with the queries!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-16-2015, 08:15 AM
  2. Replies: 3
    Last Post: 08-05-2015, 01:30 PM
  3. Select Just First Header/Detail Row
    By EddieN1 in forum Queries
    Replies: 5
    Last Post: 02-20-2014, 11:19 PM
  4. Replies: 2
    Last Post: 11-05-2013, 04:14 PM
  5. importing header and body information from text (csv) file
    By JamesL in forum Import/Export Data
    Replies: 6
    Last Post: 04-10-2012, 09:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums