By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,301 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,301 IT Pros & Developers. It's quick & easy.

opening balance - closing balance

P: n/a
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 how to do this Anyone knows how the banks do it with
statement formatting?

suggestions, ideas welcome.

Greg
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Opening Balance would be..

All transactions up to the date of 'Opening'
Closing Balance would be all transactions up to date of 'Closing'

A totals query might work well...

Mal.
"Greg" <gr************@removethis.anet.net.th> wrote in message
news:c8**********@news.inet.co.th...
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 how to do this Anyone knows how the banks do it with
statement formatting?

suggestions, ideas welcome.

Greg

Nov 12 '05 #2

P: n/a
But i don't want to see all other months. For example if user
selects Feb-2004 report should look like:

DESCRIPTION DATE IN OUT BALANCE
Opening Balance 31-01-2004 200

DescriptA 02-02-2004 20 220
DescriptB 05-02-2004 5 215
DescriptC 17-02-2004 9 224
DescriptD 19-02-2004 8 232
DescriptE 22-02-2004 20 252
DescriptF 26-02-2004 12 230

Closing Balance 29-02-2004 230

So maybe the opening balance represents many months of transactions.

Any Ideas.

Greg
"Mal Reeve" <lo*****@earthlink.net> wrote in message
news:Oc*****************@newsread2.news.atl.earthl ink.net...
Opening Balance would be..

All transactions up to the date of 'Opening'
Closing Balance would be all transactions up to date of 'Closing'

A totals query might work well...

Mal.
"Greg" <gr************@removethis.anet.net.th> wrote in message
news:c8**********@news.inet.co.th...
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 how to do this Anyone knows how the banks do it with
statement formatting?

suggestions, ideas welcome.

Greg


Nov 12 '05 #3

P: n/a
"Greg" <gr************@removethis.anet.net.th> wrote in
news:c8**********@news.inet.co.th:
But i don't want to see all other months. For example if user
selects Feb-2004 report should look like:

DESCRIPTION DATE IN OUT BALANCE
Opening Balance 31-01-2004 200

DescriptA 02-02-2004 20 220
DescriptB 05-02-2004 5 215
DescriptC 17-02-2004 9 224
DescriptD 19-02-2004 8 232
DescriptE 22-02-2004 20 252
DescriptF 26-02-2004 12 232

Closing Balance 29-02-2004 230

So maybe the opening balance represents many months of
transactions.
As Mal said, a totals query would be the answer.
This is one approach.

Create a query like
SELECT
"Opening Balance" AS transactionDescription,
#01/31/2004# as transactiondate,
sum(nz(in) - nz(out)) AS balance
Sum(0) as IN,
sum(0) AS OUT,
sum(nz(in) - nz(out)) AS balance
GROUP BY transactionDescription,TransactionDate
WHERE transactionDate < #02/01/2004#

The fields should match the ones in your month's transactions
query. This will return a one row result with the opening
balance.

Now you need to create a union query of this one and the month's
transactions query.

You use that query as the recordsource for your report.

Another approach is to just create a group header on the form,
and put a textbox in the form with a DSum() function that returns
the opening balance.

Any Ideas.

Greg
"Mal Reeve" <lo*****@earthlink.net> wrote in message
news:Oc*****************@newsread2.news.atl.earthl ink.net...
Opening Balance would be..

All transactions up to the date of 'Opening'
Closing Balance would be all transactions up to date of
'Closing'

A totals query might work well...

Mal.
"Greg" <gr************@removethis.anet.net.th> wrote in
message news:c8**********@news.inet.co.th...
> 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 how to do this Anyone knows how the banks do
> it with statement formatting?
>
> suggestions, ideas welcome.
>
> Greg
>
>




Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.