Need a step counter to increment based on existing field

[MrHiggy]

I'm trying to figure out a way of adding a counter field to a table that increments based on the values in another field, e.g.

Field A
1
1
1
2
2
8
12
12
12

The result I need should look like:-

Field A Field B
1 1
1 2
1 3
2 1
2 2
8 1
12 1
12 2
12 3

So the counter breaks and restarts based on a change in Field A.

I don't know whether I can do this inside a query or whether I need to write a module. If the latter, I've no idea how to write it.

Any assistance would be greatly appreciated.

Many thanks,
Mark

 

[Scooterbug]

What is the purpose of adding the conter to the fields? Are you trying to count up the instances of each unique value of FieldA? If so, you can use a query to count that up.

 

[ajetrumpet]

one way to do this would be to put an autonumber field in your table when you're ready to run the query, sort the query on the actual number field that you already have in place, and then write this in the QBE:

SELECT 

tbl.ANfield, tbl.number, 

DCount("number","currentqueryname",

"[ANfield] <= " & [ANfield] & 

" AND [number] = " & [number]) 

AS NumberCounter

FROM tbl

ORDER BY tbl.number;

 

[MrHiggy]

What is the purpose of adding the conter to the fields? Are you trying to count up the instances of each unique value of FieldA? If so, you can use a query to count that up.

I'm trying to note the 1st, 2nd, 3rd, etc orders placed by a customer. The first field being the customer ID. They're already listed in date order and I'm trying to calculate the average lag between orders.

So my intention is then to create a field for each order (up to five fields) and query [order2]-[order1], then [order3]-[order2], etc. This will give me the number of days between the orders which I can then average across the table (66,000 rows).

Sounds like it should be simple but I just can't figure it out.