I’m trying to create a tool to monitor employee utilization and project future utilization. To do this, I have a SharePoint list setup where employees enter a project they are working on, start date, end date, and % of their time utilized by this task. I then have this table linked to Access so that I can do analysis and reporting. The one thing I would like to do is be able to have a table where it shows staff utilization over a period of 12 weeks. The table should have each employee name in the left column and then each week across the top as additional columns. Then, if a week is between the start and end date listed above, it will use the % specified.

I can’t figure out how to do it in PivotTables or crosstab queries. If anyone can point me in the right direction, I would really appreciate it. Thanks in advance.



            Week1 2 3 4 5 6 7 8 9
Employee 1: 20% 20% 20% 20% 20% 0% 0% 0% 0%
Employee 2: 0% 0% 0% 50% 50% 50% 50% 50% 50%

Are the employees entering utilization percentages by date, once each day? How does the data look in its raw state? I think you want to aggregate the data first into weeks, and then do the xtab query. This requires two queries, not one.

You would use an extra column in the first query, and then take the datepart for the week number from the date, and do a total on the percent column. This would have to be an average, not a sum.

I used this table structure:
Table1
  UsedID - PK
  EmpName - text
  Used - number
  WorkDate - Date/Time

So the first query would look like this:
SELECT Table1.EmpName, Table1.Used, (DatePart("ww",[WorkDate])) AS Week
FROM Table1;

So with this raw data...

UseID EmpName WorkDate Used
1 Emp1 4/24/2007 20
2 Emp1 4/23/2007 20
3 Emp1 4/19/2007 25
4 Emp2 4/24/2007 30
5 Emp2 4/24/2007 25

I get this aggregation..
EmpName Used Week
Emp1 20 17
Emp1 20 17
Emp1 25 16
Emp2 30 17
Emp2 25 17

Now that I have this aggregation, I make an xtab query...
TRANSFORM Avg(Query3.Used) AS AvgOfUsed
SELECT Query3.EmpName
FROM Query3
GROUP BY Query3.EmpName
PIVOT Query3.Week;

And I get this...

EmpName 16 17
Emp1 25 20
Emp2 27.5

Is that what you wanted?






mmcdonal

Thanks for the response.

Rather then have employees enter information weekly, there is a start and end date for each record. This way, when tasks run for months, there is only one entry. Therefore, the table looks like this (although with more fields):

ID Description % of Time Start Date End Date Name
1 Task1 50.00% 10/2/2006 4/30/2007 Staff1
2 Task2 15.00% 4/2/2007 5/31/2007 Staff1
3 Task3 50.00% 4/2/2007 8/31/2007 Staff1
4 Task4 10.00% 4/9/2007 7/30/2007 Staff2
5 Task5 100.00% 6/1/2007 10/31/2007 Staff2

I currently have the grid working, but it uses a separate query for each week and then another query to join all of those together. I tried to get it work by joining the same table to itself 12 times (for the 12 weeks) which was working, but I couldn’t figure out how to limit the fields return based on the date because when I tried to enter more than one condition for the Join, it wouldn’t work for me.

If you have any ideas of how to make it simpler, please let me know.

CODE! Code always makes it simple. I would code this function.
Loop through your records, and loop through each record and get week numbers, then deposit the data in a temporary table and build the xtab from the temp table. Clear the table before each run.

Are you sorting by staff, or task, or both?

mmcdonal