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