none
Microsoft Access report subtotals not working RRS feed

  • Question

  • I am trying to create subtotals on fund name as illustrated below. The Market Value subtotals work as expected but the %ge of Portfolio Market Value subtotals all show as 0. I have seen this same aberrant behavior on several other reports. Please advise, Thanks.

    Design mode. The %ge of Portfolio Market Value field was originally formatted a Percent. I tried changing it to Standard but that didn't work either.

    

    Print preview mode.

    Monday, March 16, 2020 8:36 PM

All replies

  • The %ge of Portfolio value is being calculated in the query as the quotient of two decimal (12,2) values. The value itself displays correctly but I suspect that the sum is getting truncated. I can't alter the definition of the fields in the database. I need some way to "cast" the calculated value in the query to accommodate more decimal place.
    • Edited by nhunter Tuesday, March 17, 2020 2:39 PM
    Tuesday, March 17, 2020 2:36 PM
  • You could try computing the percentages at both levels in the query by means of subqueries.  The following is an example of a query using Northwind tables which returns the value of each order per customer, the same value as a percentage of the total value of all orders, and the value of all orders per customer, the same value as a percentage of the total value of all orders.  The latter is of course a summation of the former:

    SELECT C1.ID, Company, Orders.[Order ID], SUM([Quantity]*[Unit Price]) AS OrderValue,
        SUM([Quantity]*[Unit Price])/(SELECT SUM([Quantity]*[Unit Price])
                                                        FROM Orders INNER JOIN [Order Details]
                                                        ON Orders.[Order ID] = [Order Details].[Order ID])*100
    AS OrderValueAsPercentOfAllOrders,
        (SELECT SUM([Quantity]*[Unit Price])
         FROM  (Customers AS C2 INNER JOIN Orders ON C2.ID = Orders.[Customer ID])
         INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]
         WHERE C2.ID = C1.ID)/(SELECT SUM([Quantity]*[Unit Price])
                                                   FROM Orders INNER JOIN [Order Details]
                                                   ON Orders.[Order ID] = [Order Details].[Order ID])*100
    AS CustomerValueAsPercentOfAllOrders
    FROM (Customers AS C1 INNER JOIN Orders ON C1.ID = Orders.[Customer ID])
    INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]
    GROUP BY C1.ID, Company, Orders.[Order ID];


    Ken Sheridan, Stafford, England

    Wednesday, March 18, 2020 12:32 AM