Line Sequence based on Column Value

[jarett]

In my data set I have line key/sequence sets but they are sorted and do not represent the data that I export in the way I need. In my final query I need to be able to assign a number starting with 1 to each line of the query, and if a new "Purchase Order Number" value is found in the query, restart the line count. In my example attached the first line would have New Column "Row Number" = 1, then on line 2 (new "PurchaseOrder...#) "Row Number" = 1, line 3 "Row Number= 2, line 4 "Row Number"=3, line 5 "Row Number" = 4, line 6 "Row Number" = 1 because the value in "PurchaseOrder.." started a new value of "0062907".

 

[petertenthije]

Why would you not use the linekey that's already provided?

You can use the following formula to remove the leading zeros, should that be necessary:
=Val([TableName].[LineKey])

 

[jarett]

Why would you not use the linekey that's already provided?

You can use the following formula to remove the leading zeros, should that be necessary:
=Val([TableName].[LineKey])

Long story, we could possibly use the "line sequence" column and insert your formula, not sure if the program we are exporting to will be able to accommodate if there are missing values in a sequence. I've played around with the "DCount" function, but when I think I have a good formula and run the query Access just freezes up.

 

[petertenthije]

What you could try, is to sort the data by order number, and then add a column with autonumbering. If you are unable to change the table (I am going to guess it is ODBC linked to SAP or another ERP system), then you could copy all data sorted by purchaseorder to another table with a autonumber column.

Create a query that shows the lowest autonumber per purchaseorder. Let's call that field Min_autonumber.

Then in a second query, show all data and add a column that for each purchaseorder deducts the applicable Min_autonumber from the autonumber. That would give you a sequence from 0 to ~. You could add +1 to the formula if you need your sequence to go from 1 to ~.

Again, for this to work, make sure the list is sorted by purchaseorder before adding the autonumbering!