By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,202 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

MS Access Over Allocation Report

P: 2

This is a two part question:
1). I am trying to create a report to show overallocated resources for a project. The resource can be assigned to muptiple projects and multiple tasks. I want a report on with a subtotal of totala shcdued hours for each individual and the % over allocation (see bbelow)

Expand|Select|Wrap|Line Numbers
  1. Resource Type    Project     Task    Week1    Week 2
  2. Vendor base 45 hrs/wk    Project A    Task1    16    13
  3. Vendor base 45 hrs/wk    Project A    Task2    18    22
  4. Vendor base 45 hrs/wk    Project C    Task1    36    29
  5. Subtotal John K.                    70    64
  6. %overallocation   John K.            56%    56%
  7. FTE base 40 hours/week    Project D    Task1    20    25
  8. FTE base 40 hours/week    Project D    Task2    9    12
  9. FTE base 40 hours/week    Project D    Task3    7    5
  10. Subtotal Tommy T.                    36    42
  11. % over allocation Tommy T.            56%    56%

I can create this easily enough in a pivot table but I am unable to get the both the subtotal and the %overallocation on the same report. Any assistance on this would be greatly appreciated.

2). How can I create a gantt chart for the schedule for a resource. Right now I have to expert to excel and then re import back into visio. is there a way to create a gantt chart in ms access.
Apr 27 '15 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,397
Normally we ask for only one question per thread.

1) Gnatt Charts are not natively supported within Access.
+ If you have it installed MS Project can pull the data from MS Access and create these for you.
+ MS Kluge version to Present your data in a Gantt chart in Excel
+ Then there is the kludge approach much along the same lines as the above for MS Excel except that we would have to use shapes on the form - you will need to start a new thread if you would like that option.
+ There are third party options

2)I would suggest you take a look at the crosstab query instead of pivot tables, in ACC2013 pivot tables have been removed. If you really need the pivot table then you are looking at exporting to Excel or allowing Excel to link to the database.

Crosstab query techniques

Sum data across multiple groups by using a crosstab query
Apr 28 '15 #2

P: 2
Thanks for the suggestions zmbd. I will try thos out when I get a chance and post the results back here.
Apr 29 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.