Running Total

[SAC]

I'm trying to figure out how to make a running balance for customers.

I'm trying to get it to be something like this:

CustID Date InvoiceAmt PaymentAmt Balance
1 1/2/8 $12.00 $0.00 $12.00
1 1/3/8 $23.00 $0.00 $35.00
1 1/5/8 $12.00 $23.00

I haven't been able to figure out how to get the Balance amount.

Any ideas?

Thanks.

 

[Evi]

You need one field which tells you which invoice was the last one entered.
Your date field won't work if a customer enters more than one invoice per
day but you could make it a date + time field (and hide the time part in
your reports) or, if the data is always entered chronologically, you could
use your PK field (say TransactionID) if it is an augumenting number. The
balance is
=(Dlookup("[Balance]", "YourQueryName", "[TransactionID]<" & [TransactionID]
& " AND [CustID] = " & [CustID])) + ([InvoiceAmt] - [PaymentAmt])

This syntax assumes that CustID and TransactionID are number fields. It is
different if they are text and different again if you are using the Date
field

Evi

 

[Ken Sheridan]

Firstly, I'm sure you don't intend to but, just in case, don't be tempted to
store the balance in a column in the table. That would introduce redundancy
and the risk of inconsistent data. The following query will compute the
balances on the fly, ordering the rows by customer and within reverse date
order for each customer, i.e. latest first in normal bank statement fashion
(if you want it in date ascending order then simply take out the two
references to DESC in the ORDER BY clause). It does require a unique
TransactionID column to allow for two or more transactions by one customer on
a single day, which can be an autonumber as its values are arbitrary, so just
add the column to the table design if you don't already have an equivalent.
I've assumed the table is named Transactions for this example:

SELECT CustID, TransactionDate, InvoiceAmt, PaymentAmt,
(SELECT SUM(InvoiceAmt)
FROM Transactions AS T2
WHERE T2.CustID = T1.CustID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(PaymentAmt)
FROM Transactions AS T2
WHERE T2.CustID = T1.CustID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
ORDER BY CustID, TransactionDate DESC, TransactionID DESC;

You'll see that I've changed your Date column to TransactionDate. Date is
the name of a built in function (returning the current date) so should be
avoided as a column name as it could cause confusion and give the wrong
results. I'd advise you change the column name, but If for any reason you
can't do so, then be sure to wrap it in brackets when referring to it in a
query or elsewhere like so: [Date].

The way the above query works is to use two subqueries, one which sums all
the InvoiceAmt values for the current customer up to the current row, and one
which sums all the PaymentAmt values for the current customer up to the
current row, subtracting the latter from the former to give the balance
outstanding. Its very important that neither the InvoiceAmt or PaymentAmt
columns contain any Nulls, so be sure to set the required property of these
columns to True and their DefaultValue property to 0 (zero) in table design.
You'll have to replace any existing Nulls with zeros first, but afterwards
the process will be automatic.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

PS: Its not absolutely necessary for the transactions to be entered
chronologically for the above to work, but if the dates are being entered
without a time of day, then you might find that transactions by the same
customer on a single day are not listed in the correct sequence for that day
if they've been entered out of sequence. In fact, using an autonumber won't
totally guarantee that they are listed in the correct sequence within the day
even if originally entered in that sequence. An autonumber only guarantees
unique not sequential values. To guarantee sequence you'd need to compute
the values yourself.

Ken Sheridan
Stafford, England

 

[SAC]

Thanks!!!

You need one field which tells you which invoice was the last one entered.
Your date field won't work if a customer enters more than one invoice per
day but you could make it a date + time field (and hide the time part in
your reports) or, if the data is always entered chronologically, you could
use your PK field (say TransactionID) if it is an augumenting number. The
balance is
=(Dlookup("[Balance]", "YourQueryName", "[TransactionID]<" &
[TransactionID]
& " AND [CustID] = " & [CustID])) + ([InvoiceAmt] - [PaymentAmt])

This syntax assumes that CustID and TransactionID are number fields. It is
different if they are text and different again if you are using the Date
field

Evi

I'm trying to figure out how to make a running balance for customers.

I'm trying to get it to be something like this:

CustID Date InvoiceAmt PaymentAmt Balance
1 1/2/8 $12.00 $0.00 $12.00
1 1/3/8 $23.00 $0.00 $35.00
1 1/5/8 $12.00 $23.00

I haven't been able to figure out how to get the Balance amount.

Any ideas?

Thanks.

 

[SAC]

WOW! Thanks!!!

Firstly, I'm sure you don't intend to but, just in case, don't be tempted
to
store the balance in a column in the table. That would introduce
redundancy
and the risk of inconsistent data. The following query will compute the
balances on the fly, ordering the rows by customer and within reverse date
order for each customer, i.e. latest first in normal bank statement
fashion
(if you want it in date ascending order then simply take out the two
references to DESC in the ORDER BY clause). It does require a unique
TransactionID column to allow for two or more transactions by one customer
on
a single day, which can be an autonumber as its values are arbitrary, so
just
add the column to the table design if you don't already have an
equivalent.
I've assumed the table is named Transactions for this example:

SELECT CustID, TransactionDate, InvoiceAmt, PaymentAmt,
(SELECT SUM(InvoiceAmt)
FROM Transactions AS T2
WHERE T2.CustID = T1.CustID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(PaymentAmt)
FROM Transactions AS T2
WHERE T2.CustID = T1.CustID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
ORDER BY CustID, TransactionDate DESC, TransactionID DESC;

You'll see that I've changed your Date column to TransactionDate. Date is
the name of a built in function (returning the current date) so should be
avoided as a column name as it could cause confusion and give the wrong
results. I'd advise you change the column name, but If for any reason you
can't do so, then be sure to wrap it in brackets when referring to it in a
query or elsewhere like so: [Date].

The way the above query works is to use two subqueries, one which sums all
the InvoiceAmt values for the current customer up to the current row, and
one
which sums all the PaymentAmt values for the current customer up to the
current row, subtracting the latter from the former to give the balance
outstanding. Its very important that neither the InvoiceAmt or PaymentAmt
columns contain any Nulls, so be sure to set the required property of
these
columns to True and their DefaultValue property to 0 (zero) in table
design.
You'll have to replace any existing Nulls with zeros first, but afterwards
the process will be automatic.

Ken Sheridan
Stafford, England

I'm trying to figure out how to make a running balance for customers.

I'm trying to get it to be something like this:

CustID Date InvoiceAmt PaymentAmt Balance
1 1/2/8 $12.00 $0.00 $12.00
1 1/3/8 $23.00 $0.00 $35.00
1 1/5/8 $12.00 $23.00

I haven't been able to figure out how to get the Balance amount.

Any ideas?

Thanks.

 

[SAC]

I just ran it and it works beaufifully. Thanks again!

Firstly, I'm sure you don't intend to but, just in case, don't be tempted
to
store the balance in a column in the table. That would introduce
redundancy
and the risk of inconsistent data. The following query will compute the
balances on the fly, ordering the rows by customer and within reverse date
order for each customer, i.e. latest first in normal bank statement
fashion
(if you want it in date ascending order then simply take out the two
references to DESC in the ORDER BY clause). It does require a unique
TransactionID column to allow for two or more transactions by one customer
on
a single day, which can be an autonumber as its values are arbitrary, so
just
add the column to the table design if you don't already have an
equivalent.
I've assumed the table is named Transactions for this example:

SELECT CustID, TransactionDate, InvoiceAmt, PaymentAmt,
(SELECT SUM(InvoiceAmt)
FROM Transactions AS T2
WHERE T2.CustID = T1.CustID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(PaymentAmt)
FROM Transactions AS T2
WHERE T2.CustID = T1.CustID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
ORDER BY CustID, TransactionDate DESC, TransactionID DESC;

You'll see that I've changed your Date column to TransactionDate. Date is
the name of a built in function (returning the current date) so should be
avoided as a column name as it could cause confusion and give the wrong
results. I'd advise you change the column name, but If for any reason you
can't do so, then be sure to wrap it in brackets when referring to it in a
query or elsewhere like so: [Date].

The way the above query works is to use two subqueries, one which sums all
the InvoiceAmt values for the current customer up to the current row, and
one
which sums all the PaymentAmt values for the current customer up to the
current row, subtracting the latter from the former to give the balance
outstanding. Its very important that neither the InvoiceAmt or PaymentAmt
columns contain any Nulls, so be sure to set the required property of
these
columns to True and their DefaultValue property to 0 (zero) in table
design.
You'll have to replace any existing Nulls with zeros first, but afterwards
the process will be automatic.

Ken Sheridan
Stafford, England

I'm trying to figure out how to make a running balance for customers.

I'm trying to get it to be something like this:

CustID Date InvoiceAmt PaymentAmt Balance
1 1/2/8 $12.00 $0.00 $12.00
1 1/3/8 $23.00 $0.00 $35.00
1 1/5/8 $12.00 $23.00

I haven't been able to figure out how to get the Balance amount.

Any ideas?

Thanks.