add numbering field to a table of rows

[ralphyehle]

I am appending different types of rows to a table. I want to create a number for each row that starts with one and then consecutively numbers each row after it. The issue is that there are four different types identfied by a field with a 1, 2, 3, or 4 number code and I want the numbering to start over for each type. I am thinking to do four different queries, each appending to the same table and starting the numbering of the rows for that type at 1. I could create four tables and use an autonumber field but there has to be an easier way?

Record 1 is type 2 and the field I want to create would equal 1
Record 2 is type 1 and the field I want to create would equal 1
Record 3 is type 2 and the field I want to create would equal 2
etc.

this sounds easy but I don't find how to do it.

 

[John Big Booty]

You could use the Dmax() function +1 to do what you are after.

For an example of something similar check the sample here. You would just need to remove the criteria for re-staring each series for new months.

 

[ralphyehle]

thank you for pointing out the DMax function. Your database example opens for me missing tables, TBL_Client and TBL_Items.

 

[John Big Booty]

Sorry, those two queries are not part of that DB example and should have been deleted. Check the On Current event of the form.