DSum? Multiple Subtotals

[brucesilvers]

I'm creating a report that contains multiple subtotals. The report is divided into Rept header, Page header, Detail, Brand footer, Group footer, and Page footer. Each product is assigned to a brand, and each brand assigned to a group. I want to subtotal each Detail product in its appropriate Brand footer, then each Brand in its Group footer. How do I accomplish Brand totals over a detail section without displaying all the brands totalled (equal) in each Brand footer?

[This message has been edited by brucesilvers (edited 10-12-2000).]

 

[morning1001]

Goto Sorting and Grouping. Specify Brand and Group (each only once) in the field/expression area of the table. Below the table select "yes" for both Group Header and Group Footer for both Brand and Group.

This will show your group footer below the brand footer and not show the duplicated totals.

morning1001@excite.com

 

[brucesilvers]

Thanks for your quick reply.

I already had Sorting and Grouping set up with the correct footers (the grouping displays correctly). I added headers to see whether that made a difference. However, I'm still getting the grand total rather than the group total in each group footer.

Perhaps I am using the calculated control incorrectly: =DSum("[Total Units]","Marketing Sales") where Total Units is the field and Marketing Sales is the query upon which the report is based.

What I still get is the sum of ALL units combined (not just within their brand) for the total in each brand footer as well as the total in each group footer.

 

[Pat Hartman]

DSum() is a domain function and as you are using it, it is summing all of the members of the domain which in this case is all the rows of the Marketing Sales table. To make the DSum() work correctly, you would need to add a filter to it. However, the correct function to use in a report is Sum(). I think if you change the control to Sum([Total Units]), the problem will be corrected.

 

[Richie]

You don't need DSum just add unbound text box to group footer Source =Sum([TotalUnits])

 

[Agism]

Pat, how would I create a filter for DSum()?

I have a report that shows the phone statistics for 3 phone rotations.
Rotations 1 and 2 are agreements for 2 different companies.
Rotation 3 is supplies.

My data is grouped by date.
I would like to see the sum of calls for all rotations for each day which I added =Sum(Received) in the footer of the date.
I would also like to see the sum of calls for only Rotations 1 and 2 for each day.

I thought that DSum() would work, but as you said, it is summing all the data for Rotations 1 and 2, not just the data for each day.

My current code is: =DSum("[Receive]","qryCSA-Supplies","[UserID]<>' SUPPLIES'")

How do I get the data for each day?

 

[Agism]

Looks like I had this problem before and never solved it.

I need to use Count instead of Sum, but it is the same issue.

Group 1 - City Visited
Group 2 - Program / Software Type
Detail - Client Number

I can count the number of clients for each Program/Software type. Now I need to know how many cities were visited. It keeps counting the client numbers when I want to count the Cities. How do I count the fields in the Group Header and not the Details?

 

[Rich]

=Count([CitiesVisited]) in the Report footer

 

[Agism]

Nope, still doesn't work.

There has to be a way to create a report that looks like this:

LasVegas
Tradeshow 6
Corporate 3
Conference 5

Houston
Tradeshow 9
Corporate 5
Conference 1

Georgia
Tradeshow 15
Corporate 7
Conference 6

Cities 3

 

[Rich]

Use a totals query, and then the Report wizard, it'll do most of the work for you

 

[Pat Hartman]

You can use the report's sorting and grouping options. Add a break for city and one for show type. Use a header for city and a footer for show type. Add a sum field to sum the count by show.

If you use the report wizard to build the report, you can select the sorting and grouping options and the wizard will build the report for you. In this case select the option that says you want a summary only report.