Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
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
Old April 20th, 2007, 02:13 PM
Registered User
Join Date: Apr 2007
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%

Old 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:
  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?

Old 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.

Old 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?


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

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.