SQL Query Help

[rdchislett]

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
WHERE (((FORMHEAD.FH_ENTERED)>=#1/1/2012#))
GROUP BY Format(FORMHEAD.FH_ENTERED,"yyyy-mm"), USERS.UR_NAME
ORDER BY Format(FORMHEAD.FH_ENTERED,"yyyy-mm") DESC;

[M.Waqas Aslam]

plese first tell about your tables which are used in this query

[rdchislett]

Hi,

Sorry for the late reply.

FORMHEAD contains the header information about a form filled out. The tables are as follows:
FH_ID - GUID of record
FH_NUM - Form Number
UR_ID - User's GUID
FH_DUE - Due date entered by user for form
FH_WEIGHT - Total weight entered on the form
FH_TAGNUM - Unique number entered on form by user (represents a inventory tag number)

Table USERS just contains data about the user filling out the form, like their name.

Basically what I want to do is sum up FH_WEIGHT grouped by user id (USERS.UR_ID) and by year/month. Then I want to calculate the percentage of the users FH_WEIGHT based on the year/month group. I have data going back 6 months and want to know for each month, what the users percentage is of the whole month.

My query will sum the user's and total weight properly but I cant get it to calculate the percentage based on the month. It will only calculate on the grand total of all months.

Right now I'm exporting to Excel and manually putting in the formulas but would prefer the query to do it.