I am trying to build a payments for a schools DB.
I have tblStudents, tblClasses, tblEnrollments links them in a many to many.
I also have tblPayments which is linked with an EnrolmentsID_(Foreign Key).
Now a few problems.
I am trying to build a totalPaid field in a datasheet for each enrollment. I have the following;
=Nz(DSum("AmountPaid","tblPayments",DLookUp("Enrol lmentsID_FK","tblPayments")=[EnrollmentsID]),0)
This is only comparing the first Foreign Key in tblPayments table and totalling all the payments in to that field on my data sheet. What am I doing wrong please.
Also I want to build a payments form where I can pick a class then cascade to the students from this and then generate the correct enrollmentID from this to assign a payment. I can't seem to work this out at all.
Any help would be appreciated.