If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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:

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.