Creating a Resource Allocation Table/Form

[canderson]

Hi All,

I'm working on creating a resource allocation table for my small company. Basically, we want to be able to designate a certain number of "man-months" per functional group per month per project. Additionally, this database will hold the billing milestones that are associated with the project and we will track some health indicators to each of them.



For instance, we may have a project that is going to be running from May 15th 2014 to June 5th 2015. Based on an access project template I found, I have created some pretty good quality forms/tables that are working well for project inputs and milestone tracking.

Project Table
Lists the Project Name, Project Manager, Priority, Status, Start Date, End Date, and some baseline budget info.

Resource Groups
Lists the functional groups (Electrical Engineer (EE), Mechanical Engineer (ME), etc.) There are 9

Milestone Table
Project, Title, Priority, Status, Client Billing Amount, Schedule Risk, Technical Risk, Owner, Start Date, Due Date, and some budget lines and descriptions


Alright,

So I can't for the life of me figure out how to structure the monthly allocations in a usable table/form. This is what I would like it to look like, similar to the excel document we used to track it in.



How can I create a table that will store all of the data (Project, Resource Group, Month/Year, and Man-months) with inputs from a form that will automatically show the relevant months (between project Start Date and End Date)

It's soooo simple in excel....

Thanks!

[June7]

What you would like looks like the output of a CROSSTAB query.

Your Month/Year field should actually be Year/Month with a consistent structure like: YYYYMM (201401, 201412, etc)

Or save a full date value.

[canderson]

Sorry- I'm a total noob so this is probably a dumb question.

Assume that the values in the chart I showed were blank. Is there a way to fill in values on a query and make it write to a table?

[June7]

Not all queries are editable, so no - not through a CROSSTAB query. The nature of CROSSTAB is to aggregate data and display in a non-normalized structure. Aggregate queries are never editable and cannot be used for data input.

The table you show is not a structure for normalized data entry. It is the result of manipulating data.

[canderson]

Understand that one.

I did follow some chains through the internet to get a list of Months that fall between (and include) my Start and Finish Dates.

IE, I have a query output of

05-01-2014
06-01-2014
07-01-2014
08-01-2014

For a project starting 5/15/2014 and finishing 8/15/2014. Is there a way I can use that query output to manipulate a multiple record user form that would have a list of resources for each form that can be put into a table?


Am I taking a totally wrong approach here? I feel like maybe I should just try to incorporate an excel file into a form and then have some macro pull that data out?

[June7]

Sorry, don't understand what you are asking for.

Access is a relational database application. It's purpose is to manage and manipulate records. Critical phase of building a relational database is identifying data entities and how they relate. Build tables to fit the relationships. Then think about forms for data entry and report design for output.

Couple of principles for database design:
Don't duplicate data - I see fields Project and Milestone tables that seem to be duplication of data
Save raw data, calculate in queries - the CROSSTAB is one query type that performs calculations

What is the Milestone table for - project goals or accomplishments?

You asked about a table for Project, Resource Group, Month/Year, and Man-months. Why not a table just as that describes?

[canderson]

Alright, did a little research. My relationships are shown below.



Milestones table- Yes, project billing milestones (goals) that we need to track. This part is working very well.

Your last comment about my resource allocations- I understand that that table is exactly what I need. I was thinking about a table more like an excel sheet, not just a data dump. The key now is to create a user-accessable form that we can use.

So, if we can start over again, I think that I'm trying to find a way to create a Form that a user can input the data into my resource allocation table. Ideally it would look like the excel sheet in the OP, where it lists the functional groups of our organization and shows only the months of the project between the project Start Date and End Date. That way each project manager can input the hours that they need for their own projects. If there isn't a slick way to do that, I'd even be happy with a listing that has all 12 months and a year combo-box.

[June7]

The 'spreadsheet-like' form could not be done with a fully normalized data structure and still have the form bound to data. Could use an UNBOUND form and tons of VBA code.

Halfway between normalized/non-normalized would be a field for each month and then a field for the year.