 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

April 20th, 2007, 02:13 PM
|
Registered User
|
|
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Fill in table based on start and end date
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%
|

April 24th, 2007, 06:32 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

April 24th, 2007, 07:46 AM
|
Registered User
|
|
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

April 24th, 2007, 07:55 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|
|
 |