MS-Access: Assign records to 4 persons, with each has the same number of records

Question

The task was say I have 4000 (Or n) Customer IDs in cutomer table, and I need to assign them to 4 persons to have each of them taking care of 1000 (or N/4) customer IDs, I tried partition, but it gives me the result so wrong with 5: in each record.

SELECT CUSTOMER_ID, Partition (CUSTOMER_ID, 1, 4, (SELECT ROUND(Sum(B.C)/4,0) AS   
Employee_ID FROM (SELECT CUSTOMER_ID, Count(CUSTOMER_ID) AS C FROM CUSTOMER GROUP BY 
CUSTOMER_NAME) AS B)/4)
FROM CUSTOMER
GROUP BY CUSTOMER_ID;

Answer 1

Here is one way to do it. For test data

CUSTOMER_ID
-----------
          1
          2
          4
          7
          8
         11
         13
         14
         15
...

the query

SELECT c1.CUSTOMER_ID, COUNT(*) AS RankIndex
FROM CUSTOMER c1 INNER JOIN CUSTOMER c2 ON c1.CUSTOMER_ID >= c2.CUSTOMER_ID
GROUP BY c1.CUSTOMER_ID

will produce

CUSTOMER_ID  RankIndex
-----------  ---------
          1          1  
          2          2
          4          3
          7          4
          8          5
         11          6
         13          7
         14          8
         15          9
...

so if we do some Modulo 4 arithmetic on the COUNT(*) and name it [Employee_ID], like so

SELECT c1.CUSTOMER_ID, ((COUNT(*) - 1) Mod 4) AS Employee_ID
FROM CUSTOMER c1 INNER JOIN CUSTOMER c2 ON c1.CUSTOMER_ID >= c2.CUSTOMER_ID
GROUP BY c1.CUSTOMER_ID

we get

CUSTOMER_ID  Employee_ID
-----------  -----------
          1            0  
          2            1
          4            2
          7            3
          8            0
         11            1
         13            2
         14            3
         15            0
...