Results 1 to 4 of 4
  1. #1
    RobertIngles is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    5

    Need to allocate resource hours based on which department the worked for the most

    Hi all!

    I have data summarizing which department each resource charged hours to. I need to summarize this data down to allocate the resource to a single department and am baffled as to how I would do this.

    So if Joe Smith charged 40 hours last month to Accessory Sales department and 100 hours to Component Sales I need to allocate the entire 140 hours to Component Sales. So I need to take two lines of data, have Access recognize that the major time was spent in Component Sales and return a single line indicating 140 hours in Component Sales.

    I played with the Max function in Access but got no love

    As always, if someone could point me in the right direction it would be greatly appreciated.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    have you got a sample database available? would make this easier.

    If you are trying to do this in one query I don't think it's likely, you can do it with a combination of queries fairly easily though.

    Assuming you already have a query that is totaling each employee by department you can create another query based on that query and select the employee ID and the MAX of the hours they worked. This would give you something to help you look up which department that MAX was in by linking this query back to the original.


    so let's take a very simple table

    PK ActivityDate EmployeeID Department HoursWorked
    1 1/1/2014 1 1 10
    2 1/2/2014 1 1 8
    3 1/3/2014 1 1 8
    4 1/4/2014 1 1 6
    5 1/5/2014 1 2 4
    6 1/6/2014 1 2 10
    7 1/7/2014 1 2 10
    8 1/8/2014 1 2 10

    Create this query:
    Code:
    SELECT tblTest.EmployeeID, tblTest.Department, Sum(tblTest.HoursWorked) AS TotHrs
    FROM tblTest
    WHERE (((tblTest.ActivityDate) Between #1/1/2014# And #1/31/2014#))
    GROUP BY tblTest.EmployeeID, tblTest.Department;
    Name it Qry_Base

    Create this query:
    Code:
    SELECT Qry_Base.EmployeeID, Max(Qry_Base.TotHrs) AS MaxOfTotHrs
    FROM Qry_Base
    GROUP BY Qry_Base.EmployeeID;
    Name it Qry_MajorDept

    then create this query:
    Code:
    SELECT Qry_Base.EmployeeID, Sum(Qry_Base.TotHrs) AS SumOfTotHrs, Qry_Base_1.Department
    FROM (Qry_Base LEFT JOIN Qry_MajorDept ON Qry_Base.EmployeeID = Qry_MajorDept.EmployeeID) LEFT JOIN Qry_Base AS Qry_Base_1 ON (Qry_MajorDept.MaxOfTotHrs = Qry_Base_1.TotHrs) AND (Qry_MajorDept.EmployeeID = Qry_Base_1.EmployeeID)
    GROUP BY Qry_Base.EmployeeID, Qry_Base_1.Department;
    It should sum the total hours by employee and give you the department number in which they had the most hours (handling departments where the same number of hours are present is another issue)

  3. #3
    RobertIngles is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    5
    Thanks, i will play with your suggested code (not great with SQL)

    Here is a better representation of what I am trying to achieve:

    User Name Department Hours Log Date Log Date: Month Week
    Joe Smith Component Sales 4 May/06/14 May May Week 1
    Joe Smith Component Sales 4.5 May/05/14 May May Week 1
    Joe Smith Component Sales 4.5 May/07/14 May May Week 1
    Joe Smith Component Sales 4.5 May/08/14 May May Week 2
    Joe Smith Component Sales 4.5 May/09/14 May May Week 2
    Joe Smith Component Sales 7.5 May/12/14 May May Week 2
    Joe Smith Component Sales 7.5 May/13/14 May May Week 2
    Joe Smith Component Sales 7.5 May/14/14 May May Week 2
    Joe Smith Component Sales 7.5 May/15/14 May May Week 3
    Joe Smith Component Sales 7.5 May/21/14 May May Week 3
    Joe Smith Component Sales 7.5 May/22/14 May May Week 4
    Joe Smith Component Sales 7.5 May/23/14 May May Week 4
    Joe Smith Component Sales 7.5 May/26/14 May May Week 4
    Joe Smith Component Sales 7.5 May/27/14 May May Week 4
    Joe Smith Component Sales 7.5 May/28/14 May May Week 4
    Joe Smith Component Sales 7.5 May/29/14 May May Week 4
    Joe Smith Component Sales 7.5 May/30/14 May May Week 4
    Joe Smith Component Sales 8.5 May/16/14 May May Week 3
    Joe Smith Component Sales 9.5 May/20/14 May May Week 3
    Joe Smith Accessory Sales 2.5 May/08/14 May May Week 2
    Joe Smith Accessory Sales 2.5 May/19/14 May May Week 3
    Joe Smith Accessory Sales 3 May/05/14 May May Week 1
    Joe Smith Accessory Sales 3 May/07/14 May May Week 1
    Joe Smith Accessory Sales 3.5 May/06/14 May May Week 1
    Joe Smith Accessory Sales 3.5 May/09/14 May May Week 2
    Joe Smith Accessory Sales 7.5 May/01/14 May May Week 1
    Joe Smith Accessory Sales 7.5 May/02/14 May May Week 1
    Crosstab to:
    User Name Department May Week 1 May Week 2 May Week 3 May Week 4 Total Hours
    Joe Smith Component Sales 13 31.5 33 52.5 130
    Joe Smith Accessory Sales 24.5 6 2.5 33
    Now need to do this:
    User Name Department Total Hours Dept Alloc
    Joe Smith Component Sales 163

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the queries I gave you will give you that end result (just different field names) without using the crosstab query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-17-2014, 10:53 PM
  2. Replies: 1
    Last Post: 01-04-2014, 11:04 PM
  3. Replies: 8
    Last Post: 03-01-2013, 11:55 AM
  4. calculate rate * hours worked
    By hamish mather in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 06:14 AM
  5. Sum hours worked, grouped by dep't
    By goodfood in forum Queries
    Replies: 3
    Last Post: 05-05-2011, 06:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums