Subtract totals from 2 subreports in main report of access

[magickmann]

I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.



On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.

On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.

I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.

Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.

Expand|Select|Wrap|Line Numbers

1. =[rptP&LExpensesOverview]![AccessTotalsAmount]

Expand|Select|Wrap|Line Numbers

1. =[rptP&LSalesOverview]![AccessTotalsTotal Sales]

To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.

As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).

So how can I accomplish this?

[June7]

What section are the subforms in? What section are the main form controls in?

If you want to provide db for analysis, follow instructions at bottom of my post.

[magickmann]

I tried to get the file size smaller but could not without deleting what I think would be supporting information in the DB. I posted a stripped and full version of the DB on my website for download if you wish to take a look at it. www (dot) somboonthaifood (dot) com (slash) zip-files. P&LOverview is the report I am working on.

To answer your questions though, the subforms are in the detail section, and I tried having the totals in a variety of combinations of the page footer or report footer. Thanks

[magickmann]

Here is the solution that worked for anyone else later seeing this forum. In my main report text box controls I used the following in the control source of the two controls to bring the sub-form totals into the main form.

=[Reports]![rptP&LOverView]![rptP&LSalesOverview].[Report]![AccessTotalsTotal Sales]
and
=[Reports]![rptP&LOverView]![rptP&LExpensesOverview].[Report]![AccessTotalsAmount]

With that in there I was able to use the names of the two controls to do the math in the footer of the main report
=[TotalSalesMain]-[TotalExpenseMain]

I still don't quite understand the syntax completely of the two references for the control source (the order the the two "Reports" and "Report" references in the formula, but will research that later.

[June7]

I always give subform/subreport container control a name different from the object it holds, like ctrSales and ctrExpenses. Then this should work:

=[ctrSales].[Report]![AccessTotalsTotal Sales]
and
=[ctrExpenses].[Report]![AccessTotalsAmount]