Access 2007 SQL Subquery help

deedub84
deedub84 used Ask the Experts™
on
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)

Help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
deedub84

Author

Commented:
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
Commented:
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
GROUP BY Person
ORDER BY Person

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