Adding counter ID to set of data within group

[ingrammx]

I would like to add a "counter ID" to a set of grouped data within a query. For example, if I have 5 records, 2 with a value of A and 3 with a value of B, I would like to see the following results:

a 1
a 2
b 1
b 2
b 3

Can anyone help?

[NeoPa]

This can't be done within a query unless you have a Public Function which returns the value.
As the number of the Groups is indeterminate this would be v hard to code safely.
You could have a field in the table which could be updated by code but this also would be exposed to all sorts of drawbacks.

Essentially the answer is that queries don't support this.
Their strength is in accessing large amounts of data and data manipulation WITHIN records. Inter record relationships is completely outside their scope.

[PEB]

Hi

There was a way using subquery... but the ID of the records participated also... So if you need to distinct which one is the first and which one the second??? So really you need the ID that is autoincrement or sorted Ascending

In this forum there was a very good exemple for this... Now it isn't in my mind!

Select Group,ID, (Select Count(Group) As Result FROM Mytable AS MYT WHERE MYT.Group=[Group] AND MYT.ID<[ID]) AS Count FROM Mytable;

It was something like this as SQL

But it's just like an idea!

:)

[NeoPa]

You don't have an 'AutoIncrement' in a query surely PEB?
I'd be very interested to learn about it if it does exist.

[PEB]

Ok! The autoincrement value, I assume is the primary key of the table that is used to be enumerated!

Really this isn't an autoincrement on a query!

In fact an autoincrement in a query can be done programaticcally in a field that isn't defined as an autoincerement! But it's a lot of work! :) That sometimes doesn't matter!

:)

[NeoPa]

OK PEB.
Thanks for the prompt reply :-)