Another novice question - bank account

  • Thread starter Thread starter AccessDummy99
  • Start date Start date
A

AccessDummy99

Hi
Thanks everyone for your help with my first novice question. I have another
one.

I have two tables set up a cash at bank and also all the cash receipts that
come in to that bank account. I'm trying to work out how I can get a running
balance and am getting very frustrated. Easy in excel not very easy in access
:)

For example I would need an opening balance of $100,000, + receipt $140.00 =
closing balance $100,104. I can get it working for the first line but not
subsequent receipts.

I have tried joining a couple of queries together but whatever way I try I
can't get the balance to roll. Any ideas?


Bank
BankAccount# BankName AccountBalance
123456 Bank 1 100,000.00

Receipts
Receipt# Invoice# BankAccount# DatePaid Amount
1 101 123456 01-Jul-08 140.00
2 103 123456 10-Jul-08 88.00
 
If you want live immediate updates of the numbers, this is one of those rare
cases where Excel is better than Access. But you can do that using reports
in Access. The simples way is to do it the same as you did in excel except
run a report to create the current balance which is the totla.

- - -

"Cash at bank" isn't a table, it's one number.
 
You do not store the account balance in your database. It is always a
calculated value.
What you don't show is whether the transaction is a debit (withdrawal) or a
credit (deposit). You can either carry debits as a negative amount, use a
transaction type field to determine whether it is a debit or credit, or you
can use two different fields.
You will also want a field in your table to tell you whether the item is
open, cleared, or reconciled.
 
You should include the opening balance as a row in the Receipts table, dated
prior to or on the date of the first receipt, and with a Receipt# of 0. Then
include a subquery within a query's SELECT clause to return the balance:

SELECT [Receipt#], DatePaid, Amount,
(SELECT SUM(Amount)
FROM Receipts As R2
WHERE R2.DatePaid <= R1.DatePaid
AND R2.[Receipt#] <= R1.[Receipt#]) AS Balance
FROM Receipts AS R1
ORDER BY DatePaid,[Receipt#];

Note how the two instances of the table are differentiated by giving them
aliases R1 and R2. This enables the subquery to be correlated with the outer
query on the Receipt# and DatePaid columns to return the current balance per
row. At some time there will presumably be debits against the account
(withdrawals). You could either include these as negative values in the
Amount column or you could include a Transaction Type column, with possible
values such as 'Credit' and 'Debit', in which case the query would be along
these lines:

SELECT [Receipt#], TransactionType, DatePaid,
Amount*IIF(TransactionType = "Credit", 1,-1) AS TransactionAmount,
(SELECT SUM(Amount*IIF(TransactionType = "Credit", 1,-1))
FROM Receipts As R2
WHERE R2.DatePaid <= R1.DatePaid
AND R2.[Receipt#] <= R1.[Receipt#]) AS Balance
FROM Receipts AS R1
ORDER BY DatePaid,[Receipt#];

Alternatively you could include separate credit and debit columns. Here's
an example of a query on a Transactions table which returns the rows in
descending date order:

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

Personally I use Excel exclusively for my bank accounts.

Ken Sheridan
Stafford, England
 
Yeah, I missed the opening balance thing.
I use Quicken, have since DOS days.
--
Dave Hargis, Microsoft Access MVP


Ken Sheridan said:
You should include the opening balance as a row in the Receipts table, dated
prior to or on the date of the first receipt, and with a Receipt# of 0. Then
include a subquery within a query's SELECT clause to return the balance:

SELECT [Receipt#], DatePaid, Amount,
(SELECT SUM(Amount)
FROM Receipts As R2
WHERE R2.DatePaid <= R1.DatePaid
AND R2.[Receipt#] <= R1.[Receipt#]) AS Balance
FROM Receipts AS R1
ORDER BY DatePaid,[Receipt#];

Note how the two instances of the table are differentiated by giving them
aliases R1 and R2. This enables the subquery to be correlated with the outer
query on the Receipt# and DatePaid columns to return the current balance per
row. At some time there will presumably be debits against the account
(withdrawals). You could either include these as negative values in the
Amount column or you could include a Transaction Type column, with possible
values such as 'Credit' and 'Debit', in which case the query would be along
these lines:

SELECT [Receipt#], TransactionType, DatePaid,
Amount*IIF(TransactionType = "Credit", 1,-1) AS TransactionAmount,
(SELECT SUM(Amount*IIF(TransactionType = "Credit", 1,-1))
FROM Receipts As R2
WHERE R2.DatePaid <= R1.DatePaid
AND R2.[Receipt#] <= R1.[Receipt#]) AS Balance
FROM Receipts AS R1
ORDER BY DatePaid,[Receipt#];

Alternatively you could include separate credit and debit columns. Here's
an example of a query on a Transactions table which returns the rows in
descending date order:

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

Personally I use Excel exclusively for my bank accounts.

Ken Sheridan
Stafford, England

AccessDummy99 said:
Hi
Thanks everyone for your help with my first novice question. I have another
one.

I have two tables set up a cash at bank and also all the cash receipts that
come in to that bank account. I'm trying to work out how I can get a running
balance and am getting very frustrated. Easy in excel not very easy in access
:)

For example I would need an opening balance of $100,000, + receipt $140.00 =
closing balance $100,104. I can get it working for the first line but not
subsequent receipts.

I have tried joining a couple of queries together but whatever way I try I
can't get the balance to roll. Any ideas?


Bank
BankAccount# BankName AccountBalance
123456 Bank 1 100,000.00

Receipts
Receipt# Invoice# BankAccount# DatePaid Amount
1 101 123456 01-Jul-08 140.00
2 103 123456 10-Jul-08 88.00
Click to expand...
Click to expand...
 
ok so I can't add :) The $36 is my commission. Thanks everyone for your help
this is great.
 
Back
Top