Incremental Count in a query

[Emlou85 via AccessMonster.com]

I am trying to create a field in a query which produces an incremental number
for each duplicate record and then starts again.

For example:

Student ID Field I am trying to create

KA00002 1

KA00002 2

KA00002 3

KA00002 4

KA00002 5

MN0221 1

MN0221 2

MN0221 3

MN0221 4

NN55217 1

NN55217 2

Can anyone help?

Thank you

Emma

 

[KARL DEWEY]

This is known as Ranking In A Group.
Here is an example --
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] >Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;
Output like this --
Group Item_no Points Rank
1 10 33 1
1 1 23 2
1 9 12 3
2 3 51 1
2 2 43 2
3 5 65 1
3 4 56 2
3 6 54 3
3 7 43 4
4 8 13 1
Change --- AND Q1.[Points] >Q.[Points])+1 AS Rank
to --- AND Q1.[Points] <Q.[Points])+1 AS Rank
Or --- ORDER BY Q.[Group], Q.[Points] DESC;
to --- ORDER BY Q.[Group], Q.[Points]; depending on your needs.

 

[Ken Sheridan]

Emma:

You'll first need a column (or combination of columns) which uniquely
identify each row. If you don't already have such a column or columns then
add an autonumber column (YourID in the example below) to the table. You can
then compute the sequential numbers per student by means of a subquery like
so:

SELECT [Student ID],
(SELECT COUNT(*)
FROM [YourTable] AS T2
WHERE T2.[Student ID] = T1.[Student ID]
AND T2.[YourID] <= T1.[YourID]) AS [Field 1]
FROM [YourTable] AS T1
ORDER BY [Student ID], [YourID];

The way it works is that the two instances of the table is differentiated by
means of the aliases T1 and T2. This allows the subquery to be correlated
with the outer query on the Student ID column. The subquery can then count
the number of rows for each Student ID. By introducing the autonumber column
YourID the subquery can also be correlated with the outer query on this
column, but in this case the correlation is not, as with Student ID, on the
values matching but on the YourID values being less than or equal to the
value in the current row returned by the outer query. Consequently the count
is different for each row per student as the number of rows for that student
with YourID values less than or equal to the current value differs depending
on what the Your value is in the outer query's current row. The result is
that the count is the same as the ordinal position of the row in the set of
rows for the current student ordered by YourID. QED!

BTW you can also do this very easily in a report by grouping the report on
Student ID, including a text box in the detail section with a ControlSource
property of =1 and setting the RunningSum property of the text box to 'Over
Group'.

Ken Sheridan
Stafford, England