count based upon a field

[longhair]

good afternoon all,
have been banging my head against the wall on this and cannot figure out how to do it in access. any suggestions are greatly appreciated.
i have a table. 5 fields. data was inserted with an order by of field 3 and field 2. so, it is in a correct working order. what i would like to do is to autonumber (or do a count of) in field 5 for field 3.
essentially, as long as the active recor in field 3 is the same as the previous record, we increment the counter by 1. if the current field 3 is different from the previous record then we reset the counter to 1.
but i need to write these back into the table.
for the life of me i cannot figure out how to do this via code.
thanks and regards,
longhair

 

[dhookom]

Can you give us some sample records with actual table and field names?

Generally it is considered bad practice to store values that can be calculated. Do you have a sound justification for saving the values?

Duane
Hook'D on Access
MS Access MVP

 

[longhair]

thanks, dhookom. i understand your concern.
so here is an example:
p/t created p-key pick count
abc1 12/27/1983 0000001 5 <null>
abc2 12/27/1983 0000001 50 <null>
abc3 12/27/1983 0000001 75 <null>
abc4 12/28/1983 0000002 500 <null>
....
abc500 01/01/2009 0058348 750 <null>
the count for abc1-3 should increment... 1, 2, 3. abc4 should be 1 again.
the idea with the count is to retreive the first 10 records. top 10 should do this, but with a table of about 1 million or so records it takes too long. adding the number into a field a retrieving when that number is less than x, will speed up.
thanks,
longhair

 

[dhookom]

Do you expect this in Pick order? Do you have any idea how long this query would take for about 1 millon records?

Duane
Hook'D on Access
MS Access MVP