I'm trying to set up a query to sum a value for each user by month and then calc the percentage of that user's total vs the total month's value. My query below works as long as its limited to one month. I've tried to group my subquery the same as the main but can't figure it out.
SELECT Format(FORMHEAD.FH_ENTERED,"yyyy-mm") AS [Month], USERS.UR_NAME AS [User], Count(FORMHEAD.FH_TAGNUM) AS [Num Coils], Sum(FORMHEAD.FH_WEIGHT) AS Weight, Round(Sum(FORMHEAD.FH_WEIGHT)/(SELECT Sum(FH_WEIGHT)FROM FORMHEAD WHERE FH_ENTERED>=#1/1/2012#)*100,1) as PCT
FROM USERS INNER JOIN FORMHEAD ON USERS.UR_ID=FORMHEAD.UR_ID
GROUP BY Format(FORMHEAD.FH_ENTERED,"yyyy-mm"), USERS.UR_NAME
ORDER BY Format(FORMHEAD.FH_ENTERED,"yyyy-mm") DESC;