Can't get payments system working

[Tommo]

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.

[June7]

So this is on a form bound to tblEnrollments? Try:

=Nz(DSum("AmountPaid", "tblPayments", "[EnrollmentsID_FK]=" & [EnrollmentsID]), 0)

As for the payments form, you want to select a class then select a student already associated with that class? Maybe you need cascading comboboxes? http://datapigtechnologies.com/flash...combobox2.html

[Tommo]

Yeh that works. Thanks.

"[EnrollmentsID_FK]=" & [EnrollmentsID] What does this do please. Sorry I am new to this stuff.

Also at the bottom of the table where the new field would go it has a #Error.

It doesnt really matter but can I get rid of it easily?

[June7]

Doubtful, but try:

=Nz(DSum("AmountPaid", "tblPayments", "[EnrollmentsID_FK]=" & Nz([EnrollmentsID],0)), 0)

[Tommo]

it works. Ta

edit: no it doesnt. But thanks!