Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 20th, 2007, 02:13 PM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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%


Reply With Quote
  #2 (permalink)  
Old April 24th, 2007, 06:32 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old April 24th, 2007, 07:46 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Reply With Quote
  #4 (permalink)  
Old April 24th, 2007, 07:55 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Show Images from Start Date thru Date istcomnet Classic ASP Basics 2 May 23rd, 2008 07:12 AM
Fill DataGrid based on the selection in combobox drani C# 12 October 11th, 2007 05:41 PM
The start and the end in the DTD format janise XML 3 April 9th, 2007 01:08 PM
start date and an end date Corey Access 1 January 16th, 2006 12:37 AM
validate start and end tags saban Word VBA 1 January 13th, 2006 01:11 PM



All times are GMT -4. The time now is 09:04 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.