Guillermo said:
Hi
I need to number from 1 to "n" all the records in a table. But, when I get a
record with a different ID, then number of this record must be 1 again and
continues the sequences until the ID changes again. So, it is sequencial
numbering by ID (people in my table).
Could any one help me with this one?
Thanks,
tblSample:
RecordID GroupID
1 A
2 A
3 A
4 B
5 B
6 B
7 C
qryRankingWithinGroup:
SELECT RecordID, (SELECT Count(A.RecordID) FROM tblSample AS A WHERE
A.RecordID < tblSample.RecordID AND A.GroupID = tblSample.GroupID)+1 AS
NumberWithinGroupID, GroupID FROM tblSample;
or
SELECT RecordID, RecordID - DCount("RecordID", "tblSample", "RecordID
<=" & tblSample.RecordID & " AND GroupID <> " & Chr(34) &
tblSample.GroupID & Chr(34)) AS NumberWithinGroupID, GroupID FROM tblSample;
!qryRankingWithinGroup:
RecordID NumberWithinGroupID GroupID
1 1 A
2 2 A
3 3 A
4 1 B
5 2 B
6 3 B
7 1 C
The first query works by counting how many previous records are in the
same group within a subquery and adding one. The alternate query works
by subtracting off the number of records prior to the group from the
RecordID using DCount instead of using a subquery. I recommend that you
don't store the group sequential values within a table. Whenever
possible avoid relying on the table records being in a particular order.
James A. Fortune
(e-mail address removed)
C# Tutorials on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csref/html/vcoriCSharpTutorials.asp
