MS Access Over Allocation Report

[king7son]

Hello

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)

Resource Type    Project     Task    Week1    Week 2
Vendor base 45 hrs/wk    Project A    Task1    16    13
Vendor base 45 hrs/wk    Project A    Task2    18    22
Vendor base 45 hrs/wk    Project C    Task1    36    29
Subtotal John K.                    70    64
%overallocation   John K.            56%    56%
FTE base 40 hours/week    Project D    Task1    20    25
FTE base 40 hours/week    Project D    Task2    9    12
FTE base 40 hours/week    Project D    Task3    7    5
Subtotal Tommy T.                    36    42
% 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.

[zmbd]

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

[king7son]

Thanks for the suggestions zmbd. I will try thos out when I get a chance and post the results back here.