Opening Balance

[Guest]

Hi,
I have an activity report in my inventory DB that has a union query (or a
query made from the union query) as the record source. It takes all inbound
transactions and outbound transactions and displays them in a single report
with a running balance field as the unit balance. The report sorts and groups
by material number that has both a header and footer.

The customer requires I submit this one a month showing an opening balance
for each material number and listing only those transactions for the current
month.

When I limit the records via date parameters it loses all of the running
balance features. I've tried many different ways of adding and referencing an
"opening balance" field in the material number header but have not had any
luck.

What I end up doing is dumping the report to an Excel file and manually
adding a cell to house the opening balance and "hiding" the rows prior to the
current month and typing the opening balance number by hand (which is the
unit balance of the last line prior to the current month).

If anyone can picture this procedure with the Excel file you'll see that all
I want to do is show current month and be able to keep the running balance in
tact.

Any help would be appreciated.
Thanks.

 

[tina]

you might try creating a Totals query, grouping on the material number,
summing the transactions, and with criteria set to pull all records "prior
to the current month". base your report on this query, group the report by
the material number with a header for the section. put your transaction
total in the header section.

then create another query which includes the material number, with criteria
set to pull all transactions for this month only. create a report for this
query also. add this report to the prior report in the Detail section, as a
subreport. use the material number field in each query as the
LinkChildFields and LinkMasterFields property values in the subreport
(within the main report).

hth

 

[Guest]

ningTina,
Thanks so much for the idea. I had not tried the report/sub report idea. I
was able to configure the queries to pull the previous and current
transactions and the report/sub report shows the correct opening balance.

The only problem I have is when I do the final step of the "unit balance"
for the current month (opening balance + qty received - qty shipped) it adds
the opening balance to each line item instead of the first instance. I've
played with the sorting and grouping for the Material Number,the field with a
running sum or not...and can't seem to get it to work.

I'm so much closer than I ever been with this and I've spent more hours than
I care to mention. Thanks again for getting me this far. Let me know what you
think.

Thanks.
Len

 

[tina]

not sure what you mean by "unit balance". i assume you're adding the opening
balance in the main report to....records in the subreport? and this
calculation is in an unbound textbox control? where is the unbound textbox,
main report or subreport? and in what section?