Main Report Footer from Sub Report (1 Viewer)

[Teri Bridges]

I am looking to get the event count from my event report ininto the Module and report footer.

I need the report to show how many events there are by Module and then overall (by report). I tried to write the following, but of course I did not do well.

I do have an event Count Qry, but I could not figure out how to bring the count into the report. I have not had any luck searching online. Can anyone point me in the right direction?

 

[Pat Hartman]

Repeat the calculation in the report's footer.

 

[Teri Bridges]

Repeat the calculation in the report's footer.

Pat, I have tried that. The event report is my third nested sub-report. What appears to be happening is if there is no event for the lesson the event is showing up as a blank record and that blank record is being counted. It is like it is keeping a placeholder. I cannot figure how to get it not to show or count the blank record.

 

[Teri Bridges]

Pat, I have tried that. The event report is my third nested sub-report. What appears to be happening is if there is no event for the lesson the event is showing up as a blank record and that blank record is being counted. It is like it is keeping a placeholder. I cannot figure how to get it not to show or count the blank record.

OK I am going to back up. I am going to start with the very first report before I start nesting.

My Main report is going to list the courses by Module. When I get the report counting correctly my goal is to put in a prompt that requires the user to put in a catalog ID to filter the report to a specific course.

 

[Teri Bridges]

Is there a way to tell the report if the CourseID is blank don't count the record?

 

[Teri Bridges]

I read an article that said I could put a query in my report footer to get the count accurate. Is this a good practice?

 

[Pat Hartman]

I read an article that said I could put a query in my report footer to get the count accurate. Is this a good practice?

Normally, you would not have to do this.

Reports are sequential and they can group and count. If the report is based on hierarchically related tables, the easiest solution will be to create a main report with nested sub reports. For example. Order, OrderDetails. In order to get the summing correct, this will normally need to be a main report for Order and a subreport for OrderDetails. Why? Tax and Shipping tend to be calculated for the Order rather than for the individual line items. So, if you create a report based on a query that joins Order with OrderDetails and then add a break on order, everything will be fine except that the Tax and Shipping will be inflated since those values will occur on each line of the detail section. This won't happen if you use a subreport for OrderDetails

If you wanted a total by product. you wouldn't be able to do it with the report as it exists since it is not sorted by product and so you can't sum at a product break. Therefore, you might want to add what is commonly called a recap. That would be a report that includes the same data as the main report except it sorts and sums and groups by Product. You would put this in the Report footer so it appears once at the end of the report.