Count in a query - this must be simple!

[David Gill]

Hi

I have the following problem, which I am sure is easy to resolve for
many Access users.

A table contains the following fields:
1) Student_Id
2) Results_Id

Problem : I wish to create a select query that shows both fields, is
sorted by field one with a counter that is reset when Student_Id
changes.

Example
Student_Id Results_Id Counter
100 1222 1
100 1509 2
100 1604 3
101 1003 1
101 1093 2
102 2039 1
103 1039 1
103 3939 2

.....and so on.

Your help would be appreciated.

David

[Eric Schittlipz]

"David Gill" <d.****@shu.ac.uk> wrote in message
news:67*************************@posting.google.co m...

Hi

I have the following problem, which I am sure is easy to resolve for
many Access users.

A table contains the following fields:
1) Student_Id
2) Results_Id

Problem : I wish to create a select query that shows both fields, is
sorted by field one with a counter that is reset when Student_Id
changes.

Example
Student_Id Results_Id Counter
100 1222 1
100 1509 2
100 1604 3
101 1003 1
101 1093 2
102 2039 1
103 1039 1
103 3939 2

....and so on.

Your help would be appreciated.

David

Insert a new module and paste the following function in so the complete
module looks is as below. Make sure it compiles (Debug=>Compile) and then
save the module.

' --------------------------------------------------------------------
Option Compare Database
Option Explicit

Public g_lngStudentID As Long
'
Public g_lngCount As Long
'

Public Function RowNumber(lngStudentID As Long) As Long

If lngStudentID = g_lngStudentID Then
g_lngCount = g_lngCount + 1
Else
g_lngStudentID = lngStudentID
g_lngCount = 1
End If

RowNumber = g_lngCount

End Function
' --------------------------------------------------------------------
Your query could then be:

SELECT Student_Id,Results_Id, RowNumber([Student_Id]) AS RowNumber
FROM tblStudentResults
ORDER BY Student_Id, Results_Id
The function as posted has no error-handling and must be passed a Student_Id
which is a long integer. If there is the possibilty of a blank (null) value
in this column then you could either amend the function or use something
like RowNumber(Nz([Student_Id], 0)) to change the nulls to zeroes.

[John Winterbottom]

"David Gill" <d.****@shu.ac.uk> wrote in message
news:67*************************@posting.google.co m...

Hi

I have the following problem, which I am sure is easy to resolve for
many Access users.

A table contains the following fields:
1) Student_Id
2) Results_Id

Problem : I wish to create a select query that shows both fields, is
sorted by field one with a counter that is reset when Student_Id
changes.

Example
Student_Id Results_Id Counter
100 1222 1
100 1509 2
100 1604 3
101 1003 1
101 1093 2
102 2039 1
103 1039 1
103 3939 2

select d.Student_Id, d.Results_Id,
(
select count(*) from david as d2
where d2.Student_Id = d.Student_Id
and d2.Results_Id <= d.Results_Id
) as counter
from david as d
order by d.Student_Id, d.Results_Id