Counter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create query that will give a running counter and restart it
when the value in a field changes.

E1A3001
E1B3001
E1C3001
E1C3001
E1C3001
E1D3001

I need to have it

E1A3001 1
E1B3001 1
E1C3001 1
E1C3001 2
E1C3001 3
E1D3001 1

Thanks

agl
 
I've never been able to do it in a query, only in a report using VBA to
restart the counter.

Sam
 
Do you have another field that orders your values uniquely - a primary key
for example? Unless you can do that I am not aware of a way to do this in a
query.
 
Yes, I do have a primary key that is unique.

John Spencer said:
Do you have another field that orders your values uniquely - a primary key
for example? Unless you can do that I am not aware of a way to do this in a
query.
Click to expand...
 
I am trying to create query that will give a running counter and restart it
when the value in a field changes.

E1A3001
E1B3001
E1C3001
E1C3001
E1C3001
E1D3001

I need to have it

E1A3001 1
E1B3001 1
E1C3001 1
E1C3001 2
E1C3001 3
E1D3001 1
Click to expand...

You need at least one additional field which is unique within the
table and can be sorted in ascending order within each group:

SELECT fieldname, DCount("*", "[your-query-name]", "[fieldname] = " &
[fieldname] & " AND [uniquesortfield] <= " & [uniquesortfield])
ORDER BY [fieldname], [uniquesortfield];

Don't expect this query to run very fast for large datasets...

John W. Vinson[MVP]
 
Thanks,
Worked great.


John Vinson said:
I am trying to create query that will give a running counter and restart it
when the value in a field changes.

E1A3001
E1B3001
E1C3001
E1C3001
E1C3001
E1D3001

I need to have it

E1A3001 1
E1B3001 1
E1C3001 1
E1C3001 2
E1C3001 3
E1D3001 1
Click to expand...

You need at least one additional field which is unique within the
table and can be sorted in ascending order within each group:

SELECT fieldname, DCount("*", "[your-query-name]", "[fieldname] = " &
[fieldname] & " AND [uniquesortfield] <= " & [uniquesortfield])
ORDER BY [fieldname], [uniquesortfield];

Don't expect this query to run very fast for large datasets...

John W. Vinson[MVP]
Click to expand...
 
Back
Top