Renumber Sales Order Lines

[up4a]

I have an access program used to create a .csv file used to upload invoicing to a customer (like EDI but not standard, the Purchase Order's (PO) are not sent electronically)

The problem that I have encountered is that there are items called "KIT" items that the customer orders as a single part number but the ERP system adds lines in the sales order for the components of the 'parent' item. The .csv file needs to reference the original PO line number.

I would like to re_number the sales order lines in a query eliminating the components for the order.

So the tbl_sales has records

SO_no Line_no Comp_Item
12345 1 N
12345 2 N
12345 3 Y
12345 4 Y
12345 5 N

Hopefully the Query would return


SO_no Line_no Comp_Item PO_Line
12345 1 N 1
12345 2 N 2
12345 5 N 3

I need to keep the Original Sales order Line to then link it to the Invoice.

Any suggestions would be great!

Our back up plan is to add a field at order entry to record the PO line but since this is only used for some of the customers that would be extra (useless) work for the order entry process.

Thanks so much!

 

[PHV]

What about this ?

SELECT A.SO_no, A.Line_no, A.Comp_Item
, (SELECT Count(*) FROM tbl_sales WHERE SO_no=A.SO_no AND Line_no<=A.Line_no AND Comp_Item='N') AS PO_Line
FROM tbl_sales AS A
WHERE A.Comp_Item='N'
GROUP BY A.SO_no, A.Line_no, A.Comp_Item

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[up4a]

I am not very skilled in code so the 'A.' is confusing me. The below is what I tried in the SQL Query editor (I simplified my original table/fields but the logic is the same..) when I try to run it, it requests each parameter value.

SELECT A.SO_SalesOrderHistoryDetail.SalesOrderNo, A.SO_SalesOrderHistoryDetail.SequenceNo, A.SO_SalesOrderHistoryDetail.ExplodedKitItem, (SELECT Count(*) FROM SO_SalesOrderHistoryDetail Where SO_SalesOrderHistoryDetail.SalesOrderNo=A.SO_SalesOrderHistoryDetail.SalesOrderNo AND SO_SalesOrderHistoryDetail.SequenceNo=A.SO_SalesOrderHistoryDetail.SequenceNo AND SO_SalesOrderHistoryDetail.ExplodedKitItem<>"C") AS PO_Line
FROM SO_SalesOrderHistoryDetail AS A
WHERE A.SO_SalesOrderHistoryDetail.ExplodedKitItem<>"C"
GROUP BY A.SO_SalesOrderHistoryDetail.SalesOrderNo, A.SO_SalesOrderHistoryDetail.SequenceNo, A.SO_SalesOrderHistoryDetail.ExplodedKitItem

I apologize for my lack of skills, this forum has been so helpful in the past and this is the 1st time I have actually had to post a ??

 

[PHV]

I"d try this:

SELECT A.SalesOrderNo, A.SequenceNo, A.ExplodedKitItem
, (SELECT Count(*) FROM SO_SalesOrderHistoryDetail Where SalesOrderNo=A.SalesOrderNo AND SequenceNo=A.SequenceNo AND ExplodedKitItem<>"C") AS PO_Line
FROM SO_SalesOrderHistoryDetail AS A
WHERE A.ExplodedKitItem<>"C"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

"the 'A.' is confusing me"

You simply susbsitute your SO_SalesOrderHistoryDetail with the letter A in your Select statement in "FROM SO_SalesOrderHistoryDetail AS A". It is an allias.

You may as well say:
FROM SO_SalesOrderHistoryDetail AS XYZ
and wherever you have the name of SO_SalesOrderHistoryDetail, you use XYZ instead.

Have fun.

---- Andy

 

[up4a]

The Query runs but it returns 1 for every PO_Line, it doesn't "re-number" (Thanks again for the help!)

 

[up4a]

I got this to work but it is really slow..

SELECT A.SalesOrderNo, A.SequenceNo, A.SkipPrintCompLine
, (SELECT Count(*) FROM SO_SalesOrderHistoryDetail B Where A.SalesOrderNo=B.SalesOrderNo AND A.SequenceNo>=B.SequenceNo AND A.SkipPrintCompLine<>"Y") AS PO_Line
FROM SO_SalesOrderHistoryDetail AS A
WHERE A.SkipPrintCompLine<>"Y" AND A.SalesOrderNo Is Not Null
ORDER BY A.SalesOrderNo, A.SequenceNo

Maybe I can make a table from this sub-query and then only append it going forward to save some time.