Pivot Table calculation combining row detail and totals

[dhmlofi]

I am working on a Pivot Table and have a calculation that I'm very stuck with. I've been trying to combine a calculated detail field with a calculated total field and getting nowhere. If this isn't possible, I'd like to know how I can achieve the calculation I need?
So in the image here, I want to get a currency value based on a percentage. In the image, I want to divide 5 (detail) by 61.5 (total) and multiply by 1130, giving me a value of $91.87.
I also tried making the total field a percentage of column value and that didn't work either.

My source is:
SELECT Employees.EmployeeName, Sectors.SectorName, PayPeriod.PayPeriodNo, TimeTracking.Hours, EmployeeCosts.Wage, [Wage]+[MSP]+[CPP]+[EI] AS TotalCost
FROM Sectors INNER JOIN (PayPeriod INNER JOIN ((Employees INNER JOIN EmployeeCosts ON Employees.ID = EmployeeCosts.EmployeeID) INNER JOIN TimeTracking ON Employees.ID = TimeTracking.Employee) ON (PayPeriod.PayPeriodID = TimeTracking.PayPeriodNum) AND (PayPeriod.PayPeriodID = EmployeeCosts.PayPeriodID)) ON Sectors.ID = TimeTracking.Sector
GROUP BY Employees.EmployeeName, Sectors.SectorName, PayPeriod.PayPeriodNo, TimeTracking.Hours, EmployeeCosts.Wage, [Wage]+[MSP]+[CPP]+[EI];



I'd appreciate some advice please. Thanks in advance.

[June7]

I've never used pivot tables/charts (be aware, MS has removed this functionality from Access 2013).

Might have to do an aggregate query that returns the grand total and include that query in your main query. Without a join clause, the grand total record will join to every record of the other dataset and will therefore be available on each line for use in calculation.

[dhmlofi]

I've never used pivot tables/charts (be aware, MS has removed this functionality from Access 2013).

Might have to do an aggregate query that returns the grand total and include that query in your main query. Without a join clause, the grand total record will join to every record of the other dataset and will therefore be available on each line for use in calculation.

Thanks June7. I'm perhaps a step on the right road but am clearly still missing something.

I've created a second aggregate query as you suggest (called Query2). When I run the query I get the totals I need. In my sample data I get 17 rows:

SELECT TimeTracking.Employee, TimeTracking.PayPeriodNum, Sum(TimeTracking.Hours) AS SumOfHours
FROM Employees INNER JOIN TimeTracking ON Employees.ID = TimeTracking.Employee
GROUP BY TimeTracking.Employee, TimeTracking.PayPeriodNum;

I've added this into my main query (Query1). However I now get duplicate rows showing, that seem to account for the number of rows in my new query, i.e. each of my detail value is repeated 17 times. This probably makes more sense by showing you my screen shots.



My source for the main query is now:
SELECT TimeTracking.PayPeriodNum, Employees.EmployeeName, Sectors.SectorName, TimeTracking.ID, TimeTracking.Hours, [Wage]+[MSP]+[CPP]+[EI] AS TotalCost, Query2.SumOfHours
FROM Query2, Sectors INNER JOIN (PayPeriod INNER JOIN ((Employees INNER JOIN EmployeeCosts ON Employees.ID = EmployeeCosts.EmployeeID) INNER JOIN TimeTracking ON Employees.ID = TimeTracking.Employee) ON (PayPeriod.PayPeriodID = TimeTracking.PayPeriodNum) AND (PayPeriod.PayPeriodID = EmployeeCosts.PayPeriodID)) ON Sectors.ID = TimeTracking.Sector
GROUP BY TimeTracking.PayPeriodNum, Employees.EmployeeName, Sectors.SectorName, TimeTracking.ID, TimeTracking.Hours, [Wage]+[MSP]+[CPP]+[EI], Query2.SumOfHours;

[June7]

Since Query2 is not just a single record but has grouping by Employee and PayPeriodNum, will have to JOIN Query2 with records of main query on those two fields.

[dhmlofi]

Since Query2 is not just a single record but has grouping by Employee and PayPeriodNum, will have to JOIN Query2 with records of main query on those two fields.

It worked! Thanks. Yes, when I created the joins in the query it reduced my number of records. My pivot table now works too when I add the calculated detail field.

You've helped me in a query on this forum before. So thanks again!