Calculations on a report

[thebigman]

Hi folks I have a report as attached. My totals all work but my final totals at the bottom dont sum up. I wonder if somebody could help.
Thank you.

 

[DHookom]

I'm not sure how we can help without seeing the complete expressions in you text boxes. Are the totals adding the names of text boxes from the detail section?

The use of many DSum()s doesn't look efficient to me. Your table structures or other may dictate this.

Also, the alignment (or lack of) and spacing is difficult for us suffering from OCD.

 

[plog]

I've got the right help, but not help you are going to want to hear--I think you have set up your database tables incorrectly.

The little we have to go on raises a lot of red flags. The Detail section doesn't use any data from the report. It uses DSums which have their own data source. Further, DSums don't just retrieve data, they aggregate it which is odd for the Details section. Lastly, you've hard coded your categories into the report (e.g. Equipment, Vehicles, etc). That information should be stored in a table itself, not hard coded and extracted with a series of DSums.

The ultimate report you create should use neither Dsums nor hard coded labels in the Details section--it should pull of its data from the reports datasource.

Can you post a screenshot of your tables Relationships so we can see the structure of your tables?

 

[Ken Sheridan]

Using multiple domain aggregation functions is a very unusual and inefficient way to build a report like this. Normally the data from the base tables would be aggregated in a query. The following is a simple example using Northwind Developers Edition tables as an example. Firstly the relevant tables are joined so that the values from ProductCategoryName, UnitPrice and Quantity columns for all order lines can be returned. The query is then grouped by the ProductCategoryName column and the UnitPrice and Quantity values are multiplied to give the total amount per order line. The summation of the results of these multiplications is then returned with the SUM operator:

SELECT
    ProductCategories.ProductCategoryName,
    SUM(
        [OrderDetails].[UnitPrice] * [OrderDetails].[Quantity]
    ) AS Amount
FROM
    (
        ProductCategories
        INNER JOIN Products ON ProductCategories.ProductCategoryID = Products.ProductCategoryID
    )
    INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
GROUP BY
    ProductCategories.ProductCategoryName;

This query can then be used as the RecordSource of a report, returning each row in the report's Detail section. The Amount column can then be summed in an unbound control in the report footer with a ControlSouce property of =Sum([Amount]).

In your case you would create two reports, one for credits, the other for debits. These could then be embedded in an unbound report as side-by-side subreports.

To return the balance you'd use an unbound control in the parent report's footer, whose ControlSource property would reference the unbound text boxes in each subreport's footer, subtracting the value of one from the other. To improve the layout you could also reference the two unbound text boxes in the subreports' footers in text box controls in the parent report's footer, and hide the original text boxes in the subreports' footers by setting their Visible property to False (No).

 

[LarryE]

Hi folks I have a report as attached. My totals all work but my final totals at the bottom dont sum up. I wonder if somebody could help.
Thank you.

It looks like some kind of accounting project with different expense and income categories, but it is unclear. Please tell us what this is and what you are attempting to keep track of. Also, as mentioned earlier, we will need to see your table and relationship design.