473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Statements - Opening balance closing balance question.

I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmount (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)

tblAccountBalance (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)

My goal is to have a report that shows the following:

Opening balance
Transactions for this period
Closing balance

When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.

But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalance. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.

Thanks

Paul
Jun 27 '08 #1
3 5147
"Paul H" <go****@comcraft.freeserve.co.ukwrote in message
news:e5**********************************@l64g2000 hse.googlegroups.com...
I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmount (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)

tblAccountBalance (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)

My goal is to have a report that shows the following:

Opening balance
Transactions for this period
Closing balance

When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.

But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalance. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.
Thanks
Paul
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>
Paul,
I think your tblAccountBalance may be redundant and a poor design idea. It
appears to be 100% build-able from tblTransactions. Thus, it should not be
needed. If you really want to use such a table then it shoulde be used
temporarily for a process or report and then deleted afterwards. The
AccountBalance field is merely the sum of TransactionAmount from
tblTransactions for records <= to the desired StatementDate.

You can create your desired statement report using just the tblTransaction
table. The report would have a filter set to the desired ClientID and
TransactionDate between the desired OpeningDate and the desired ClosingDate.
The report header would include a control for the OpeningBalance as:

= DSum("TransactionAmount","tblTransactions","Client ID=" & ClientID & " And
TransactionDate<#" & dtOpeningDate & "#")

The detail section of the report would list all of the transactions for the
client during the period.

Finally, the report footer will show the ClosingBalance with a control of:

= OpeningBalance + Sum(TransactionAmount)

Fred Zuckerman
Jun 27 '08 #2
On 7 May, 14:07, "Fred Zuckerman" <Zuckerm...@sbcglobal.netwrote:
"Paul H" <goo...@comcraft.freeserve.co.ukwrote in message

news:e5**********************************@l64g2000 hse.googlegroups.com...
I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmount (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)

tblAccountBalance (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)

My goal is to have a report that shows the following:

Opening balance
Transactions for this period
Closing balance

When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.

But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalance. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.
Thanks
Paul

Paul,
I think your tblAccountBalance may be redundant and a poor design idea. It
appears to be 100% build-able from tblTransactions. Thus, it should not be
needed. If you really want to use such a table then it shoulde be used
temporarily for a process or report and then deleted afterwards. The
AccountBalance field is merely the sum of TransactionAmount from
tblTransactions for records <= to the desired StatementDate.

You can create your desired statement report using just the tblTransaction
table. The report would have a filter set to the desired ClientID and
TransactionDate between the desired OpeningDate and the desired ClosingDate.
The report header would include a control for the OpeningBalance as:

= DSum("TransactionAmount","tblTransactions","Client ID=" & ClientID & " And
TransactionDate<#" & dtOpeningDate & "#")

The detail section of the report would list all of the transactions for the
client during the period.

Finally, the report footer will show the ClosingBalance with a control of:

*= OpeningBalance + Sum(TransactionAmount)

Fred Zuckerman
Thank you for your post Fred.

Two years from now I will have to sum two years worth of data to
arrive at the opening balance if I omit tblBalances. Surely
tblBalances is required to reduce overhead when I run the report?

Paul
Jun 27 '08 #3
On May 7, 5:06*am, Paul H <goo...@comcraft.freeserve.co.ukwrote:
I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmount (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)

tblAccountBalance (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)

My goal is to have a report that shows the following:

Opening balance
Transactions for this period
Closing balance

When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.

But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalance. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.

Thanks

Paul
tblBalance is a good idea, allows you to archive transactions after a
period of time by updating the accountBalance with the value of the
archived transactions

but it is not a good design idea to use it as an accumulator of past
invocations of the report

I'm guessing your scenerio is
for clientId 1, the balance is 0
run report which marks some transactions ($1000) with a statement
date
update accountBalance to $1000, balanceDate is ????

one month later run report with $2000 of transactions and mark
another date
update accountBalance to $3000, balanceDate is ???

now what happens when the client wants a statement for the past 2
months ?
how do you rebuild the first statement is the account balance is no
longer 0 ?

now, if you use accountBalance as only the client opening balance,
you'd change the report's opening balance to be accountBalance +
sum(any transactions prior to the statement date), so
for clientId 1, the balance is 0 + 0
run report which marks some transactions ($1000) with a statement
date

one month later run report with $2000 of transactions and mark
another date
opening balance is 0 + 1000 (sum of prior transactions)

to rebuild first statement, opening balance is 0, you'd select all
transactions
where the statement date <= a 'reprint' date

ditto for a statement that spans multiple months

three years from now, you'd have a function that moves transactions to
an archive table, or deletes if you don't care to about the details
and updates the accountBalance to say $100,000
and sets the balanceDate to the archive date

now the next statement you create
for clientId 1, the balance is $100,000 + 0
run report which marks some transactions ($2500) with a statement
date

etc
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jean-Fran?ois Lacrampe | last post by:
Hello, I want to write a _very_ simple text parser that would replace a string like: "This is text with /italics/, *bold* and _underline_." and generate automatically something like this: ...
3
by: Greg | last post by:
On my report I want to have an opening balance signifying all transactions up to the month selected and detailed transactions for the month selected and then a closing blance. I'm perpelexed...
34
by: Jeff | last post by:
For years I have been using VBA extensively for updating data to tables after processing. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have...
2
by: Prakash | last post by:
I have 2 tables ... Customer_Master: Cust-Code, Cust_Name Customer_Transactions: Cust_Code, Date, Details, Debit, Credit I would like to generate a report in the foll manner, say from...
1
by: gbstclair | last post by:
I new to c programming and was wondering if someone can please help me to understand how to get the following printf statements to align up the dollar amounts when displayed? printf ("\n\n*** Bank...
7
by: Martien van Wanrooij | last post by:
I have been faced a couple of times with the situation that I wanted to write a script and was worried about a too frequent opening and closing mysql connections. To give some examples: 1)I...
6
by: nikhil | last post by:
Hello every one, Can any one tell me the syntax for opening and closing a file in associated program from C++. For an instance I got a MS word file. How can I open it in MS word from a console...
4
by: kessa | last post by:
Hi Guys, I've recently had a problem with my site displaying a "system resources exceeded" error message ... and whilst searching this site for a solution (which I think I've now found) I came...
2
by: eeze | last post by:
Hi Everyone I am very new to access.Can someone help me.I am trying to create a form with a daily Opening balance using sql.My tbl looks like this below.I willbe very grateful. Date Opening...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.