IT looks like you have this relationship in place. Do you?
ProjectSetup::PS_Name = PaymentRec::PR_Name
While I do not recommend using a Name field as the match field in a relationship (a serial number ifled in ProjectSetup matching to a number filed in PaymentRec is a much better option), I'll go with it for now.
Define PR_pkPRID as an auto-entered serial number field.
In Manage | Database | relationships, make a new table occurrence of PaymentRec by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as Prev_PAYMENTSREC.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
PaymnentsRec::PR_Name = Prev_PAYMENTSREC::PR_Name AND
PaymnentsRec::PR_pkPRID ≠ Prev_PAYMENTSREC::PR_pkPRID
This relationship matches to every record in PaymentsRec with the same value in PR_Name EXCEPT for the current record.
PR_Prev_Bal can now be defined as:
ProjectSetup::Amount - Sum ( Prev_PAYMENTSREC::PR_Paid )
The problems with using a name as a match field in a relationship are:
Names are not necessarily unique
Names are subject to change due to client/management directives and also to correct possible data entry errors. If you create a new record in ProjectSetup, log several related records in PaymentsRec and then have to change the project name, your related records in PaymentRec will disappear if you don't carefully update them to the same new name as you do for the matching record in ProjectSetup.
Once agian, you are da man!!!!
And yes, I agree about using serail numbers - I will go through my database and change it.