create a counter

[Fasiano]

hello there. i am working on my final year project and i stuck in the.
if anybody could help me please do it.

i have created a query named "general" which gets its values, "Studentid"
"class" "date" from tables which are updated from forms.
what i need is to create a counter:

This is what i have:
StudentID Class Date
S021N0032 EENG233 1/1/2007
S021N0032 EENG233 3/3/2007
S021N0032 EENG233 5/6/2007
S033N0033 EENG111 1/4/2007
S033N0033 EENG111 15/6/2007

This is what i want:
StudentID Class Date Counter
S021N0032 EENG233 1/1/2007 1
S021N0032 EENG233 3/3/2007 2
S021N0032 EENG233 5/6/2007 3
S033N0033 EENG111 1/4/2007 1
S033N0033 EENG111 15/6/2007 2

When the StudentID or Class changes no matter the date, the counter should
add one to its previous value.

i have created a module which always returns the value 1 to my counter
thats it:

Option Compare Database
Global StudentID As String, MyCount As Integer, GlobClass As String
Function ReturnRecordCount(StudentID As String, Class As String)
If StudentID <> GlobStudentID Or Class <> GlobClass Then
GlobStudentID = StudentID
GlobClass = Class
MyCount = 1
Else
MyCount = MyCount + 1
End If
ReturnRecordCount = MyCount
End Function

please send me a reply to correct my module or create a new one.
thank you.

 

[Ken Sheridan]

Forget the module; you can do this entirely in a query by using a subquery to
count the rows where the student and class are the same as the current
student and class and the date is on or before the current date,
differentiating the two instances of the query with aliases:

SELECT StudentID, Class, [Date],
(SELECT COUNT(*)
FROM General AS G2
WHERE G2.StudentID = G1.StudentID
AND G2.Class = G1.Class
AND G2.[Date] <= G1.[Date])
AS Counter
FROM General AS G1
ORDER BY StudentID, Class, [Date];

The above uses the General query in the FROM clauses of both the outer and
subquery, but you could of course use the base tables in each, joined
appropriately. The above assumes that a student does not attend the same
class twice on one day. If that were the case then you'd need the date to
include the time of day to distinguish them.

You can do this more efficiently in a report based on the General query,
however. Group the report by StudentID and by Class. Put all the bound
controls in the detail section along with an unbound text box whose
ControlSource property is =1 and whose RunningSum property is Over Group.
Don't include an ORDER BY clause in the query for this, the report will
ignore it; use the report's internal sorting and grouping mechanism. Unlike
the query the report would number rows correctly if a student attended the
same class twice ion one day without the need to include the time of day.

BTW I'd suggest you avoid Date as a column name; it is the name of the built
in Date function, so could be confused with that. Wrapping the name in
square brackets should avoid any problems, but its nevertheless best to use a
more specific name like AttendanceDate.

Ken Sheridan
Stafford, England