Share via

Trying to use total query to calculate percentage of whole!

Question

Thursday, June 14, 2012 10:14 PM

Ok, I'm keeping track of overtime, which involves the reason for overtime and amount of overtime.  I have a table that has records for each day overtime is worked .  Each record in this table includes, name, date, hours worked, and reason.  I have a table set up that just includes "reasons" so I can have a combobox display a list of reasons to choose from. 

With that being said, I created a total query and it works as I think it should, I have two columns in the query, Reason and Hours worked.  It is grouped by Reason and Hours worked is summed.  The query returns items below, grouped correctly:

Illness         20

Vacation      34

Training       20

Other          40

I'm wanting to add a column so I can calculate the percent of each reason of the total overtime so I can display this in a form.  So what else do I need to do here, I'm totally lost.

Thanks

All replies (4)

Thursday, June 14, 2012 11:09 PM âś…Answered | 4 votes

Hi Kevin,

This is what it looks like in the query builder;

"Total" is a sub query that will return the Total hours from the Overtime table.  Of course if you wanted to restrict this to a date range you could use query parameters and the sub query would look like;

(select sum(Hours) from Overtime where OvertimeDate between [StartDate] and [EndDate])

If you are not familiar with query parameters in Access 2003 you select "Query - Parameters" from the menu and create the parameters.  Given the parameter names are "StartDate" and "EndDate" the user will be prompted to enter these whenever the query is run.  These values will then be used wherever "[StartDate]" and "[EndDate]" appear in you query.  The query would then look like;

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT Overtime.Reason, Sum(Overtime.Hours) AS SumOfHours, (select sum(Hours) from Overtime where OvertimeDate between [StartDate] and [EndDate]) AS Total, Sum([Hours]/[Total]) AS Percentage
FROM Overtime
WHERE (((Overtime.OvertimeDate) Between [Startdate] And [EndDate]))
GROUP BY Overtime.Reason;

Of course these values could also be taken from fields on a form by replacing "[StartDate]" and "[Enddate]" in the query with the name of the form and control where these values may be obtained.  The query would then look like

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for changing her words.

Thursday, June 14, 2012 10:29 PM

The following query will do what you want;

SELECT Overtime.Reason, Sum(Overtime.Hours) AS SumOfHours, (select sum(Hours) from Overtime) AS Total, Sum([Hours]/[Total]) AS Percentage
FROM Overtime
GROUP BY Overtime.Reason;

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for changing her words.

Thursday, June 14, 2012 10:36 PM

Ray, thanks a bunch.  I'm not at all up on my SQL.  Could you please take a few minutes and explain what I would have in my query builder?  I do understand the query builder, I'm just trying to actually learn what your telling me.   Thank you sir!

PS.  Also, your example, is that using my existing totals query, or is it a new select query?

Thursday, June 14, 2012 11:17 PM

Thank you very much.  I got it and it works great.

  • Last updated on