Another novice question - bank account

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


--
AccessDummy99

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.



"AccessDummy99" wrote:

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


--
AccessDummy99

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.
--
Dave Hargis, Microsoft Access MVP


"AccessDummy99" wrote:

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


--
AccessDummy99

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" wrote:

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


--
AccessDummy99

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


"Ken Sheridan" wrote:

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" wrote:

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


--
AccessDummy99

"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."

BTW, what's the name of your bank? I just want to know so I don't go there!
Depositing $140 there apparently only increases an account by $104! $36 is a
pretty hefty service fee for making a deposit!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200808/1

I wrote the software for that bank. The $36.00 goes to my account
--
Dave Hargis, Microsoft Access MVP


"Linq Adams via AccessMonster.com" wrote:

"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."

BTW, what's the name of your bank? I just want to know so I don't go there!
Depositing $140 there apparently only increases an account by $104! $36 is a
pretty hefty service fee for making a deposit!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200808/1

ok so I can't add The $36 is my commission. Thanks everyone for your help
this is great.
--
AccessDummy99


"Klatuu" wrote:

I wrote the software for that bank. The $36.00 goes to my account
--
Dave Hargis, Microsoft Access MVP


"Linq Adams via AccessMonster.com" wrote:

"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."

BTW, what's the name of your bank? I just want to know so I don't go there!
Depositing $140 there apparently only increases an account by $104! $36 is a
pretty hefty service fee for making a deposit!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200808/1