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

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

SELECT intStudentID,
Count(dtPayDue) As LatePayments,
Sum(intAmountDue) As AmountDue
FROM tbCoursePayments
WHERE dtDatePaid Is Null
GROUP BY intStudentID ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"JC" wrote:

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