Summing values from sub reports

[emik]

I have a query that gets all the data I need. In that 1 query I have values A-Z. I need to break it down so that it shows A-M and then have the N-Z on the bottom (I have certain fields that need to be grouped and sorted, so I can't just sort ascending).

So I created 2 queries, 1 for A-M and the other for N-Z. I created a report with the record source being the query A-M and then a subform of the query N-Z. I've been unable to give a final total at the bottom. I need to keep all 3 queries because the user will want to export all the data

-query 1 A-Z (used when user exports all data)
-query 2 A-M
-query 3 N-Z

I tried =Sum([field_on_main]+[sub_report_name]![field])

but it always prompts me to enter a value for "sub_report_name" or invalid error.

Any suggestions on how to do this or a better way of going about it?

Thanks.

 

[barny2006]

this is an example that when a command button is pushed, based on what boxes they have field, runs a different query. child39, being a subform that changes with options.
frm_fund_activity is a form and a query is associated with it.

If options = 6 Then
Child39.SourceObject = "frm_fund_activity"
End If

If options = 8 Then
Child39.SourceObject = "frm_FY_activity"
End If

 

[James529]

Since I don't know any better, haven't tried it, but why not let each report (main and sub) keep it's own totals and store the results in hidden fields. Then, at the report end, the footer say, add the fields together and display them eh?

 

[emik]

That's similiar to what I've done. Each report has a total in the footer, so I pass the value from the sub report to a global variable which I then use to do the equation on the main report.