Cheque Book Database

[George]

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.

 

[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

 

[George]

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