Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47

    Unanswered: line items for an export

    I am exporting PO's as a query into Excel for import into an AS400 (JD Edwards). For the Detail export, I need to have line items. In other words, all PO detail will have 1 item number 1000 for the first item of the detail but if there is more than 1 item on a PO, I need the second to have a line item of 2000, the third one of 3000, etc.

    Could anyone give me an idea how to do it?

    Thanks

    Terri
    Terri Gavin

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm not sure I follow.

    Are you saying that your source PO detail doesn't have line numbers?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47
    No, it has auto numbers. What I need is if the export has 3 PO's on it with multiple items on each, the export has to say
    PO Item Line Item
    1...... A..........1000
    1.......B..........2000
    1.......C.........3000
    2.......Z.........1000
    2.......Y.........2000
    3.......M........1000

    etc. I don't know how to get these line items to count for just that one PO number, then start all over with the next PO.

    thanks
    Terri
    Terri Gavin

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah.

    That's easier to do directly in Excel, once you've got the data out of Access. Use the following formula to generate the numbers:
    =IF(A2=A1, C1+1000, 1000)
    This assumes the following:
    • Your export has no empty rows at the top
    • You have a header row
    • The PO number is in column A
    • Your item line number needs to go in column C

    If you have no header row, you can always manually number the first PO's first line at 1000, and use the formula from the next row.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I was able to create what I think would work for you. I have looked at it twice (some yesterday and some today) to get my head around it. Here is a link that might help explain the process. Look at solution #3 that is what I am basing my solution on:

    Calculating Running Totals - SQLTeam.com

    I created a table called tblJunk and it had two fields in it OrderID and Product. And I filled it with some nonsense data. Then I created a select query that selected the data from tblJunk and added an additional field LineItem:1000. So for each record I appended 1000. Then the following SQL calculates the LineItem ID. Here is the SQL:

    SELECT a.OrderID, a.Product, Sum(a.LineItem) AS LineItemID
    FROM qryJunk AS a, qryJunk AS b
    WHERE (((b.OrderID)=[a].[OrderID]) AND ((b.Product)<=[a].[Product]))
    GROUP BY a.OrderID, a.Product;

    I have never tried this before and it was a little hard to understand what is going on. If you have alot of data this approach may be slow since all records from the table are being compared to all the records of itself. Let me know if you need any help. If so, it might help if you provided table names and field names.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •