Re: For Marshall Barton

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Marsh,

I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

"Marshall Barton" wrote:

znibk wrote:
Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear


I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • RE: Question about wording and position overall total not correct
    ... beginning balance, I am concerned that the beginning balance from the query ... subreport or report that I put ... error under each of the txtSumExpenses and txtSumIncome ... Name and Control Source. ...
    (microsoft.public.access.reports)
  • Re: Eliminate zero values/pass calculated value to new calc field
    ... In my report, I have a Calculated field that sums Beg.Balance and Deposit Amt, less fee amt and/or check amt. ... But then each successive period Ending Balance is off, because the calc still uses the original Beg.Balance amt. ... If there's too much detail here, you might at least consider using the "Running Sum" feature of an Access Report, though you can't do any calculations using it. ... The Queries listed here form the basis for a Report that lists individual transactions by date, running balance for the date, and ending balance. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Question about wording and position overall total not correct
    ... How can I just get the report to pull the beginning balance of the date I ... Here's what I know about retrieving values from a subreport into a main ... assume this text box is "txtSumFromsrptOne" ...
    (microsoft.public.access.reports)
  • Re: Calculating an inventory balance at a "point in time"
    ... are summed together to get a beginning balance. ... create a report based on this query ordered by PartNumber and TransDate. ... The inventory balance of the ... line 2 needs to contain a calculation that subtracts the ...
    (microsoft.public.access.reports)
  • RE: Question about wording and position overall total not correct
    ... Here's what I know about retrieving values from a subreport into a main ... A subreport is a control on your main report ... assume this text box is "txtSumFromsrptOne" ... beginning balance, I am concerned that the beginning balance from the query ...
    (microsoft.public.access.reports)