2007 - count no of records in payments table for each student

[JC]

Hi Everybody,

I have a table of all the payments a student is scheduled to make during
one semester - tbCoursePayments - with the following principal fields:

intStudentID intPayRef dtMonth dtPayDue intAmountDue dtDatePaid dtAmountPaid

I have a form with a subform in datasheet view to list all the late
payments.

I can list those students who have payments still outstanding by
checking if dtDatePaid is empty.However, this shows some students more
than once. I would like to count the number of outstanding payments that
each student has and the total still outstanding.

1 - John - 2 late payments - $250
2 - Richard - 1 late payment - $135

I'd like to do this for all the students who have late payments.

Any help would be greatly appreciated,

Jeff C

 

[Jerry Whittle]

SELECT intStudentID,
Count(dtPayDue) As LatePayments,
Sum(intAmountDue) As AmountDue
FROM tbCoursePayments
WHERE dtDatePaid Is Null
GROUP BY intStudentID ;