Cheque Book Database

[Guest]

Hello gurus again,

I need to develop a personal cheque book database and I don't know how to
obtain the available balance for each transaction, e.g.

1/1/06 - Opening Balance 100
1/1/06 - Withdrawal 50 - Balance should show 50
2/1/06 - Deposit 150 - Balance should show 200

Please note I need this for each transaction - in order to print a statement
for a specific period. Not to get the totals of withraeals and totals of
deposits and then to make the calculation.

Thank you in advance

 

[Arvin Meyer [MVP]]

In a continuous form, you can use a footer to display the "grand" total or
final balance of the records in the dataset. In a report, you can get what
you want by adding another amount column and setting the Running Total to
"over All"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[(PeteCresswell)]

Per George:

I need to develop a personal cheque book database

Quicken? Microsoft Money? Vast numbers of developer manhours available for
very little cost.

 

[PC D]

Available Balance = Opening Balance - (Sum of withdrawals on or prior to
current date) + (Sum of deposits on or prior to current date)

 

[Guest]

Assuming that you have a uniquely valued column as the primary key of the
table, e.g. an autonumber TransactionID, and that you have separate Credit
and Debit columns and that the latter contains positive values not negatives,
use the following query as the RecordSource for a form:

SELECT TransactionID, TransactionDate, Credit, Debit,
DSum("Credit – Debit", "YourTable", "TransactionDate <= #" &
Format([TransactionDate],"mm/dd/yyyy") & "# And TransactionID <=" &
[TransactionID]) As Balance
FROM YourTable
ORDER BY TransactionDate, TransactionID;

For a report omit the ORDER BY clause and use the report's internal sorting
mechanism to order the report by TransactionDate and then by TransactionID.

Ken Sheridan
Stafford, England

 

[StopThisAdvertising]

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
(e-mail address removed)

--
To Steve:
Why PC D ? why not just PC DataSheet?
You think that changing names is vital for you? It seems like a second nature but ...
No-one wants your advertising/job hunting here!
Over 750 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (still rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[Guest]

Dear All, - Thank you for all answers.

Dear Ken,

I have tried the following
SELECT T_ChequeBook.TransactionID, T_ChequeBook.TransactionDate,
T_ChequeBook.Credit, T_ChequeBook.Debit, DSum("Credit –
Debit","T_ChequeBook","TransactionDate <= #" &
Format([TransactionDate],"mm/dd/yyyy") & "# And TransactionID <=" &
[TransactionID]) AS Balance
FROM T_ChequeBook
ORDER BY T_ChequeBook.TransactionDate, T_ChequeBook.TransactionID;

And I gett an #Error in Balance
Can you further help please?
Thank you in advance

Ο χÏήστης "Ken Sheridan" έγγÏαψε:

Assuming that you have a uniquely valued column as the primary key of the
table, e.g. an autonumber TransactionID, and that you have separate Credit
and Debit columns and that the latter contains positive values not negatives,
use the following query as the RecordSource for a form:

SELECT TransactionID, TransactionDate, Credit, Debit,
DSum("Credit – Debit", "YourTable", "TransactionDate <= #" &
Format([TransactionDate],"mm/dd/yyyy") & "# And TransactionID <=" &
[TransactionID]) As Balance
FROM YourTable
ORDER BY TransactionDate, TransactionID;

For a report omit the ORDER BY clause and use the report's internal sorting
mechanism to order the report by TransactionDate and then by TransactionID.

Ken Sheridan
Stafford, England

Hello gurus again,

I need to develop a personal cheque book database and I don't know how to
obtain the available balance for each transaction, e.g.

1/1/06 - Opening Balance 100
1/1/06 - Withdrawal 50 - Balance should show 50
2/1/06 - Deposit 150 - Balance should show 200

Please note I need this for each transaction - in order to print a statement
for a specific period. Not to get the totals of withraeals and totals of
deposits and then to make the calculation.

Thank you in advance