find total based on 2 criteria

[Guest]

hello, ive got a query thats supposed to total up the amount of rent that
someone owes for a student tenancy database and use this result as the
control source for a data entry form, its to help inform the user of how much
the student owes.

the form is based on the following query:

SELECT Students.Duration, RentPayment.RentPaymentNo, RentPayment.StudentRef,
RentPayment.AmountPayed, RentPayment.ReceiptNo, Students.completed,
Students.DiscountedRentalPayment
FROM Students INNER JOIN RentPayment ON Students.StudentRef =
RentPayment.StudentRef;

The students table holds records on the student and the rentpayment table
holds records on each rental payment that student does, on the students form
is the total amount that student owes and each rentpayment should subtract
from this total till it reaches 0.

I need another field on this form which takes that student.discountedtotal
figure and subtracts the sum of all rentpayments for that student up to that
receipt (so that when you go back to previous receipts it reflects the true
value at that point).
I got a query to work which does the same thing and ive shown this below:

SELECT Sum(rentpayment.AmountPayed) AS SumOfAmountPayed
FROM rentpayment
WHERE (((rentpayment.ReceiptNo)<[forms].[receiptform].[receiptNo]))
GROUP BY rentpayment.StudentRef
HAVING (((rentpayment.StudentRef)=[forms].[receiptform].[student]));

I've been frustrating myself using a Dsum() to to figure out this total and
then subtract it from the discounted total. or is there a way to take the
value from this query and use it in the other, i dont have any common fields
that are shown and when i do add one then it seems to throw this query!

would welcome any suggestions

Amit

 

[[MVP] S.Clark]

Maybe break it down to 2 or more queries, and string together as needed.
Like one query does the Sum, then link that to a query with the discounts.

DSum() may work as well, as is pretty easy to master. Try playing with it
in the Immediate Window(Ctrl-G) first.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting