By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Adding counter ID to set of data within group

P: 3
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?
Oct 27 '06 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 27 '06 #2

PEB
Expert 100+
P: 1,418
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!

:)
Oct 27 '06 #3

NeoPa
Expert Mod 15k+
P: 31,494
You don't have an 'AutoIncrement' in a query surely PEB?
I'd be very interested to learn about it if it does exist.
Oct 27 '06 #4

PEB
Expert 100+
P: 1,418
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!

:)
Oct 27 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
OK PEB.
Thanks for the prompt reply :-)
Oct 27 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.