Sequential Numbering based on Criteria

[BNeff]

MLO_ID	Tally_1
1	1
1	2
1	3
1	4
2	1
2	2
3	1
3	2
3	3

I have a database with the MLO_ID column shown above filled in. It can have between 3-100 repeated values - I've kept it rather short here, but suffice to say it's a long column. I want a query that fills in the Tally column as shown, sequentially numbering up for each SAME MLO_ID value, and restarting at every new one.

Currently I do this by copying the MLO_ID into excel, with the MLO_ID header in A1, and in B2 writing the formula 'IF(A2=A1,B1+1,1)', and then I copy the values back into this Tally_1 column.



However I would like this in an access query so I can hand it off to coworkers without having to explain extra steps. Is this possible?

[June7]

Requires a unique identifier field in table - autonumber should serve. One way uses DCount() domain aggregate.

SELECT *, DCount("*", "tablename", "MLO_ID=" & [MLO_ID] & " AND [ID]<" & [ID]) + 1 AS Tally FROM tablename;

[BNeff]

That worked very well!

So MLO_ID should have been MLI_ID, MLO_ID is actually the unique field.

This brings up a table.. I need to figure out how to update an existing column, as software on top of this needs to use that column to name files in a specific way - so I can't just have a temporary queried table, I need an actual update to the column involved. But I imagine I can just throw an update in there to update the pre-existing Tally_1 field?

[June7]

Should work. Give it a try, maybe on copy of table first.

What software?