Running Total not working

[Prufrock]

I have got close to getting a running total working on a query and then
appearing as a subform in a form but the totalling is happening across
all records not the ones that should be selected from the main form.

I have a form and subform based on a query. When I select an ID number
a sub form shows me following fields:

[PaymentNumber] [ID][Date] [AmountDue] [AmountPaid] [AmountOwing]
[TotalOwing]

I have a calculated field in the query that takes AmountPaid from
AmountDue. Sometimes not all is paid. This gives me Amount Owing.

Now I have used a further calculated field in query that attempts a
running sum of the TotalOwing. I am getting totalling occuring but when
I get the information in the subform for a tenant by selecting them in
the main form it shows they have no AmountOwing but the TotalOwing is a
figure (as if it is totalling across all payment numbers and nor
specific to the tenantid. I have the form and subform linked by Tenant
ID.

My current expression is
TotalOwing:
Format(DSum("RentOwing","qryRentPayments","[PaymentNumber]<=" &
[PaymentNumber] & ""),"$0.00")

Can someone help me to ensure that when I select a tenant in main form
the records I see ( which I do) are only theirs and also the running
total (TotalOwing) matches their AmountOwing amounts? Thanks very much

 

[RoyVidar]

Prufrock wrote in message

I have got close to getting a running total working on a query and
then appearing as a subform in a form but the totalling is happening
across all records not the ones that should be selected from the main
form.

I have a form and subform based on a query. When I select an ID
number a sub form shows me following fields:

[PaymentNumber] [ID][Date] [AmountDue] [AmountPaid] [AmountOwing]
[TotalOwing]

I have a calculated field in the query that takes AmountPaid from
AmountDue. Sometimes not all is paid. This gives me Amount Owing.

Now I have used a further calculated field in query that attempts a
running sum of the TotalOwing. I am getting totalling occuring but
when I get the information in the subform for a tenant by selecting
them in the main form it shows they have no AmountOwing but the
TotalOwing is a figure (as if it is totalling across all payment
numbers and nor specific to the tenantid. I have the form and
subform linked by Tenant ID.

My current expression is
TotalOwing:
Format(DSum("RentOwing","qryRentPayments","[PaymentNumber]<=" &
[PaymentNumber] & ""),"$0.00")

Can someone help me to ensure that when I select a tenant in main
form the records I see ( which I do) are only theirs and also the
running total (TotalOwing) matches their AmountOwing amounts? Thanks
very much

I think you could probably alter the domain aggregate condition to
include this condition, perhaps

DSum("RentOwing","qryRentPayments","[PaymentNumber]<=" [PaymentNumber]
& " AND [Tenant ID] = " & [Tenant ID])

If the above represents the name of the field, and form control holsind
the Tenant ID.

In stead of also using the format function within the query, which I
think will convert the number to a string/text, perhaps try using the
format property of the form control in stead.

Else, you could experiement with a query using subquery. Note - the
below is only typed, not tested, so if you try it, you might need to
tweak it a little.

Select t.PaymentNumber, t.ID, t.[Date], t.AmountDue, t.AmountPaid,
t.AmountDue-t.AmountPaid as AmountOwing,
(select sum(s.AmountDue-s.AmountPaid)
from YourTable s
where
s.PaymentNumber <= t.PaymentNumber AND
S.[Tenant ID] = t.[Tenant ID]) as TotalOwing
From
YourTable t