advice on better report design - opening/closing balance

  • Thread starter Thread starter Sag73
  • Start date Start date
S

Sag73

Hello Group,

I have a table which contains transactions (in and out) of a stock item. The
objective was to create a report based on a user specified period (date1 to
date2) to display, the opening balance at date1 , the individual trasactions
between date1 and date2, and the closing balance at date2.

I have designed my report to be based on a query which extracts the required
transactions between date1 and date2 and their particulars fill the detail
section. I have then added a subreport in the header which is based on
another query which sums all transactions up to and excl date1 grouped by
warehouse location to give the opening balances (by warehouse). I have
repeated this latter process for the closing balance as well in order to
tally that opening balances + transactions = sum of closing balance of each
store.

The report works fine but I got a feeling that this is not the best solution
as I basically have two identical subreports based on the same query but to
which different date parameters are passed.

Any advice is welcome.

Report looks like this

At 30/06/2005
Warehouse #1 2,500
Warehouse #2 1,000

-------

3,500
Transactions during period IN OUT
01/07/2005 100 200
04/07/2005 50
05/07/2005 80
15/07/2005 150
18/07/2005 400
28/07/2005 600
29/07/2005 200 800
31/07/2005 1,000
-------------
800 1,800
--------------

-1,000

-------

2,500

=====
At 31/07/2005
Warehouse #1
2,000
Warehouse #2
500
 
Sag73 said:
I have a table which contains transactions (in and out) of a stock item. The
objective was to create a report based on a user specified period (date1 to
date2) to display, the opening balance at date1 , the individual trasactions
between date1 and date2, and the closing balance at date2.

I have designed my report to be based on a query which extracts the required
transactions between date1 and date2 and their particulars fill the detail
section. I have then added a subreport in the header which is based on
another query which sums all transactions up to and excl date1 grouped by
warehouse location to give the opening balances (by warehouse). I have
repeated this latter process for the closing balance as well in order to
tally that opening balances + transactions = sum of closing balance of each
store.

The report works fine but I got a feeling that this is not the best solution
as I basically have two identical subreports based on the same query but to
which different date parameters are passed.

Report looks like this

At 30/06/2005
Warehouse #1 2,500
Warehouse #2 1,000
-------

3,500
Transactions during period IN OUT
01/07/2005 100 200
04/07/2005 50
05/07/2005 80
15/07/2005 150
18/07/2005 400
28/07/2005 600
29/07/2005 200 800
31/07/2005 1,000
-------------
800 1,800
--------------
-1,000
-------

2,500

=====
At 31/07/2005
Warehouse #1
2,000
Warehouse #2
500
Click to expand...


Add a calculated field to the subreport query:
Period: IIf(transdate < startdate, 1, 2)

Then add a text box named txtPeriod1 to the report header
section with the expression =1. Set the first subreport
control's Link Master property to txtPeriod1 and Link Child
property to Period. Note that the subreport may(?) need to
have a text box bound to the Period field.

The subreport control in the report footer section would
have nothing specified in both its Link Master and Child
properties.
 
Back
Top