Numbering items in a table

[Ethvia]

Hi, I'm trying to number items in a table using a repeating range. For instance if I have 100 items in a table, I want to do a make table query to take those items and then number them 1-10 repeating (so when I get to the 11th item it's starts over numbering 1-10, the same when it gets to the 21st item). In the end, for the sake of this example, I would have 10 items assigned #1, 10 items assigned #2, etc.

I've played around trying to do this multiple ways and aren't having much luck. Does anyone have a suggestion of how to do this?

thanks!

 

[Oeldere]

It can be done like this.

Book1
	A	B
1	name	value
2	A	1
3	B	1
4	C	1
5	A	1
6	B	1
7	C	1
8	A	1
9	B	1
10	C	1
11	A	1
12	B	2
13	C	2
14	A	2
15	B	2
Blad13

Cell Formulas
Range	Formula
B2	=INT((ROW()-2)/10)+1

 

[AhoyNC]

If you are trying to get {1,2,3,4,5,6,7,8,9,10,1,2,.......}

=MOD(ROW(A1)-1,10)+1

 

[Ethvia]

It can be done like this.

Worksheet Formulas Cell Formula B2 =INT((ROW()-2)/10)+1 <tbody> </tbody>

<tbody>
</tbody>

thanks for the response, but I'm trying to do this on a table in MS Access, not Excel. Or am I not understanding what you're writing?

 

[Ethvia]

Thanks Ahoy, but I'm trying to accomplish this in MS Access.

What you're saying is correct, I want 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,etc.... but I'm trying to add it as a field based off of an existing table via a query

 

[Oeldere]

No, it is an excel solution.

Sorry I don't read enough, that you posted it in the access group.

 

[Ethvia]

that's ok, I appreciate your response

 

[Micron]

No expression will do what you want in a make table query because the record does not exist when an expression would need to use it. You could probably run an update query on your new table using the expression given by Ethvia, using an auto number field in your new table. You would substitute Row() with the auto number field name. The rest of the expression is common stuff.
In code, you could also create a recordset based on the table, then loop through it and set the field value to what the expression returns. That's a bit more complicated though.