Sum a count from a subreport in the main report

[bodhi23]

:banghead:
I'm giving myself a headache... If someone would be so kind as to point me in the direction of a solution, I would be grateful.

I have a report which should count the number of students and provide the number of hours of service for each major, and then the total of each for the college.

A basic query pulling the hours, colleges, and majors from our contact hours query sufficed to sum the duration for each major, and then sum the hours for the entire college.

To count the students tho, required a subreport. As the Count function counted each record, not unique students.

So, I have a report, grouped on College, then grouped on Major, displaying the total duration for each major, and the number of students, via subreport, in each major (this data is in the Major group header, because in the Detail section, the majors don't group).
In the group footer for each college, a total of the hours for the entire college appears.

I cannot, for the life of me, figure out how to sum the number of students for each college from the subreport to the College group footer. I have scoured the web for 3 days, and every likely expression fails.

I thought at first, DLookup would be helpful, but DLookup only returns the first result of a query.
I thought perhaps I could use the expression to reference a field in a subreport (=Report![Summary]![Summary Subreport]![SalesTotal])
That didn't work either.
I tried DSum, with #Error as my only result.

No luck.

I don't really want to create a sum field in the subreport, because that would involve grouping, which would screw up the format of the report. We're trying to be as concise as possible. There has to be another way.

The fields in question are:
[College], [Major], [Duration] in the main report
and [College], [Major], and [CountofStudentID] in the subreport.
The subreport name is subrpt_UsageStuMajor
The main report name is Usage By College

Your assistance is appreciated, in advance.
Thanks.

 

[vbaInet]

To count the students tho, required a subreport. As the Count function counted each record, not unique students.

Was this the only reason why you created a subreport or you have other reasons?

Why don't you count the number of students per Major in this group's Footer section?

 

[bodhi23]

Because it counts the number of records in our hours table, not the number of individual students.

(i.e.: There are 133 Biology majors, but a count based on the hours report returns 1488, which is the number of records from Biology majors - not the number of students)

A suggestion for forcing a count of individual students rather than referencing a subreport would be just as useful.

 

[vbaInet]

Upload a cut down version of your db let me see how you've layed it out and advise.

 

[bodhi23]

Just the objects necessary to run the report in question, with a significant portion of data removed.

(The original tables are not my construction, inherited the database. Working on more normalization, it's just a big project.)

Suggestions for getting a unique student count on that report, and a unique student total by college are welcome.
Thanks!

 

[RainLover]

Suggest that you post using A 2003. We don't all have the latest. Nor do some want it.

The Function that could help would be DSum. You must have the syntax incorrect.

 

[Pat Hartman]

An easy way to do counts like this is to create a query that generates the counts and then join to that query in the main report's RecordSource. You'll have the count and you can put it anywhere you want.

Although domain functions such as DSum() and DCount() can be used to calculate what you need, they are quite inefficient and can be very slow if they need to run hundreds or thousands of times in a report.

 

[vbaInet]

Sorry, only had time to look at your report.

See attached, rptCollegeUsage

By the way your tables are not properly normalised that's why you were finding it difficult to calculate.

 

[bodhi23]

RainLover, Pat Hartman, I tried all manner of D*** functions, they seem to only report the first result.

RainLover, apologies, I'll try to remember that next time.

vbaInt, thank you for your help. I'll take a look at what you've got. I know the tables aren't normalized properly, it's something I'm working on at every stage of revision.

 

[Pat Hartman]

1. Did you try the query solution I proposed?
2. If your domain functions are pulling in only a single record, their where argument is incorrect.

 

[bodhi23]

I've got the report counting the number of students per major, and calculating the number of hours per major. It will also total the number of hours per school/college (the larger group).

What I can't get it to do, is reference the subreport of student count by major, in order to obtain a total per school/college (the larger group).

In talking to my director, we've decided the hours are more important, so we're gonna go with it as is. But I'm sure there's a way to get the group to total both students and hours...

Ex:
................................................Students.................Hours
School: College of Arts and Sciences
Majors:
.............Biology...............................3.......................25.00
.............Chemistry...........................2.......................17.00
.............Psychology.........................5.......................36.00

Totals (College of Arts and Sciences)..10......................78.00

The student count, to avoid duplication, is a subreport. I have been unsuccessful in referencing the subreport to get a total per school/college. That "10" figure is what I'm looking for...

 

[vbaInet]

You were shown in the database I attached a way of doing it all using queries. I mentioned looking at rptCollegeUsage for the solution. It seems you didn't look at it.