Search code examples
ms-accessautonumber

MS Access - restricted, repeating auto-number

I'm trying to implement a restricted auto-number within multiple tables which can be used as common ground to facilitate work allocation.

Currently I have a range of queries that select a number of pieces of work sorted by descending value. I'd like to implement a fixed auto-number within these tables that repeats in the following format:

1,2,3,4,5,6,7,8,9,10,11,1,2,3,4,5,6,7,8,9,10,11...etc.

This is to allow me to link each piece of work in the table to an owner assigned a number from 1 - 11.

I'm more than happy to receive alternate suggestions!

Thanks in advance. J.

Solution

  • You can run a query:

    Select 
    YourTable.Id, 
        (Select Count(*) 
        From YourTable As T 
        Where T.ID < YourTable.Id) Mod 11 + 1 AS 
    ID11
From 
    YourTable;

    where Id is the primary key.