Access 2007 SQL Subquery help

deedub84 used Ask the Experts™
I have a table of hours by month by person by project: i.e.

Person1  Project1  Month1Hrs   Month2Hrs .... Month12Hrs
Person1 Project2  Month1Hrs  Month2Hrs ... Month12Hrs
Person1 BeneLeave Month1Hrs  Month2Hrs ... Month12Hrs

I need a query that will show the percent of the total by person where the top number in the percent is the sum of all projects but one (called Beneleave) and the bottom number is the total of all the projects including Beneleave

The query results will have one line per person with a percent for each month (I'm after what percent of work time (all the projects except beneleave) is of all time recorded (which includes the leave time in Beneleave)

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Here's my attempt at it.  Access is highlighting the subquery saying there is a problem:
SELECT tblGanttHours.RptGrp, tblGanttHours.WorkGroup, tblGanttHours.FullN, tblGanttHours.LastN, Sum(tblGanttHours.Total) AS FullYear, Sum(nz([Month01])) AS Jan, Sum(nz([Month02])) AS Feb, Sum(nz([Month03])) AS Mar, Sum(nz([Month04])) AS Apr, Sum(nz([Month05])) AS May, Sum(nz([Month06])) AS Jun, Sum(nz([Month07])) AS Jul, Sum(nz([Month08])) AS Aug, Sum(nz([Month09])) AS Sep, Sum(nz([Month10])) AS Oct, Sum(nz([Month11])) AS Nov, Sum(nz([Month12])) AS [Dec], Sum(nz([Month01]))/(Select Sum(nz([Month01])) FROM tblGanttHours as S1 HAVING S1.FullN = tblGanttHours.FullN AND S1.RptGrp = tblGanttHours.RptGrp AND S1.WorkGroup = tblGanttHours.WorkGroup AND S1.LastN = tblGanttHours.LastN GROUP BY S1.RptGrp, S1.WorkGroup, S1.Project, S1.FullN, S1.LastN) as Percent01
FROM tblGanttHours
Having tblGanttHours.Project Not In ("BenSpecial")
GROUP BY tblGanttHours.RptGrp, tblGanttHours.WorkGroup, tblGanttHours.Project, tblGanttHours.FullN, tblGanttHours.LastN
ORDER BY tblGanttHours.FullN;

Open in new window

Top Expert 2010
Your SQL does not match to your example.  Anyway, something like this:

SELECT Person, 
    Sum(IIf([Project] <> "Beneleave", Nz([Month1], 0), 0) / (Sum(Nz([Month1], 0)) + 0.00000000001) AS Jan,
    Sum(IIf([Project] <> "Beneleave", Nz([Month2], 0), 0) / (Sum(Nz([Month2], 0)) + 0.00000000001) AS Feb,
    Sum(IIf([Project] <> "Beneleave", Nz([Month3], 0), 0) / (Sum(Nz([Month3], 0)) + 0.00000000001) AS Mar,
you get the idea
    Sum(IIf([Project] <> "Beneleave", Nz([Month12], 0), 0) / (Sum(Nz([Month12], 0)) + 0.00000000001) AS Dec,
FROM SomeTable

Open in new window

The "+ 0.00000000001" is to avoid any possibility of a division by zero.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial