Generate restarting autonumber

[dark11984]

I need to design a table or query that can be exported to excel then copied into a write protected upload spreadsheet.

The fields in the upload spreadsheet are:
Order#
Sequence#
SKU
Qty

The sequence number is 1, 2, 3 , etc for each SKU then restarts at each new order #.

I can't figure out how to assign the sequence number in my query or table before exporting it.

Any help would be much appreciated. Thanks.

 

[CJ_London]

simplest way in your query is to use the dcount method so perhaps something like

sequence: dcount("*","myTable","Order=" & Order & " AND SKU<=" & SKU)

assumes your SKU is a numeric value, if it is text then use
sequence: dcount("*","myTable","Order=" & Order & " AND SKU<='" & SKU & "'")

you will need to order your query by Order, SKU

Note using non alphanumeric characters such as # is not recommended, you can get unexplained errors

 

[June7]

Calculating in query a sequential ID number for each group is not simple in Access. It is a common topic.
Review http://allenbrowne.com/ranking.html

 

[Pat Hartman]

If you need the sequence to be permanent, you need to assign it when the record is added. Here's a sample that shows how to create two kinds of sequence numbers.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk