Subreport Total on Main Report 1

[Vittles]

Similar to other posts, I have a main report with a subreport that I need to use to capture a 'grand total' in the main report footer.

The main report is based on information from a query (combining the main table and another many-side table) and then the subreport is pulling a count of the records from yet another many-side table in relation to each main table key field. The subreport works in that is does bring the number of records. The part that doesn't work is the 'grand total' field that is brought from the subreport into the main report footer.

If I put a 'sum' field in the subreport footer and refer to it on the main or do some of the other variations to accomplish this, the main footer continues to only pull the total number of records from the last main table entry.

Example below:
Person Name: # of Mentor(s) Number of labs:
Smith 1 9
Johnson 0 11
Jones 0 15

Report footer:
Total People: 3 Total Mentor(s): 1 Total labs: 15 (should be 35)

Does anyone have any ideas? I have been playing with this one until I think I am just banging my head on the wall. =)

 

[BSman]

Here are a few ideas:

1. You could write a grouping query on the main report that would calculate the total and put it in the control to display the grand total. This is probably the easiest solution if you can easily define a query that will select the appropriate records to count.

2. An even easier variation on 1 is to make the data source for the grand total control on you main report using the DCount function.

3. A more involved method would be to define a public variable that you would use to calculate the running total on your subreport. For your grand total on your main report just put the value of this variable into your grand total control, either through a bit of code or through a function you would define to return the value of the variable. (Unfortunately, you can't make the data source of the control the variable itself, that's why you would need a function.)

Bob S.

 

[Vittles]

How do I do your first option?

I should also relay that the main report is pulled using a multi-select list of person's names then it goes to the main report query source for part of the data.

I have tried to use a query for the subreport that counts the records in the query and groups by the key field of the main report, but it still does pull the total numbers together..... It seems like I can either have an unbound subreport where it pulls a total of all records or a bound subreport and pull a total per main table key field...

I know there must be a complexity added by having the one to many table relationships, as I can do this with a database that has just one table.

Would it make a difference if I had all of the tables involved linked in the main report query and then use all subreports to pull the various counts? If there are any examples that use subreports off of a many sided table, that would be great as sometimes by seeing it work, I can figure out why mine isn't....

 

[BSman]

I'll use DCount, since it's essentially the same concept as the grouping query and you can enter it right in the data source.

Assumptions:

1. On your main report is a control called txtPerson containing the identifier of the person (I'll assume it's text).

2. Your detailed table/query is called "Details"

3. "LabID" is the name of the column in "Details" that contains refers to that lab.

4. "Person" is the name of the column in "Details" that contains the name of the person.

5. "txtPerson" is the name of the control on your main form that contains the current person.

For the number of labs control you would have this source:

DCount("[LabID]","[Details]","[Person] ='" & me.txtPerson & "'")

The single quotes are needed because I'm assuming Person is a text value.

Bob S.

 

[Vittles]

What you listed does work as far as listing the number of labs per person. It replaces the need for a subreport for that number, which is very nice.

How can I list a grand total number of labs based on those numbers to the report footer? I tried just using the Lab Count as the control source for the control in the footer, but it still only brings one person's number.

Person Name: Number of labs:
Smith 9 (using DCount function)
Johnson 11 (using DCount function)
Total Labs: 9

 

[BSman]

If there is a column (ex: labdate) in your details table that will provide the correct report total, then group on that and change the third part of DLookup so it only selects the one date. If you are working on a range of dates, then add a dummy column to your grouping report (ex: GrpBy: 1) that will always be the same value. Group on that and include the correct criteria so only those details that were used on the report are selected. For example, if the report was printed for a date range, then the DLookup criteria will be that date range.

Bob S.

 

[Vittles]

There isn't a field in any of my tables that I can use to unite the results - really wish there was. Right now I have changed the main report source query to include all involved tables with their related primary key fields in the query. When you add a dummy "1" column - you mean to add it to the main report query, right? Then add a top grouping level to the report of that '1' field? If so, I tried that & it didn't change the results of the grand total field...

Sorry for being a bit slow on this one....really have been using Access for a while... I just haven't done this or used DLookup before.

Using the multi-select form filters the data, so the actual criteria that pull the correct records are not present in the underlying query, it is just put into the 'filter' property.

 

[dhookom]

I would use a text box on your main report that displays the total from the subreport:
Name: txtTotLabsRunSum
Control Source: =srptCtrlName.Report.txtTotalControlOnSub
Running Sum: Over All

Then add a text box to your main report's footer:
Control Source: =txtTotLabsRunSum

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Vittles]

Thank you both for taking the time to work with me on this question! Sometimes I think I get so into the development that I don't think of all the options clearly anymore.

The running sum worked very nicely. I made the textboxes in the group/detail area invisible and then just added the grand total textboxes to the report footer.

Thanks again!