Resource Planning Database

[andy_dyer]

Hi - not sure where to post this... so apologies if it's in the wrong place...

I am started a new job and have been handed a very clunky excel spreadsheet with columns for each day going off infinitely....

I need to be able to by project, select what resource type and what FTE is required for each day...

This sounds to me a really simple database and i can get all my supporting tables and relationships sorted but am stuck how I would best structure a table and form to capture this.

I would also like to map key milestones i here somewhere too so I can somehow display the resources required around key activities...

Any inspiration much appreciated!

 

[DavidAtWork]

Andy, I think you might need to explain/post a bit more in terms of 'project specs' before help can be offered. Perhaps a list of fieldnames and what you're trying to achieve would be a start.
David

 

[jdraw]

Andy,

I agree that you haven't provided enough background info for readers to provide specific advice. The best I can do is suggest a link that goes through the design process based on some statement of what you are trying to do.

I hope this is useful to you.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[andy_dyer]

Thanks to you both

In simple terms

There will be a
Project Table with project names on it
Dept Table with dept names on it
Resource Type Table with types of resources on it i.e. manager, admin etc

Then I need to find a simple way of choosing a project and then for that project selecting by dept the types of resources that are required to work on the project and then what FTE per day for specific days.

I've attached a excel sheet with some random data on...

The problem with keeping this in excel is that the one day per column strategy is getting a little silly and I though access could offer something more robust...

Does that help?

 

[DavidAtWork]

From what I can see from your sample.xls you'd require the following tables, some of the tables will data tables and some will be used purely as a lookup list to make a selection.
Project table with appropriate fields to store what project details you need
Dept table to store a list of dept's
Resources table to store a list of resource types
A main table which could be called something like ResourceAllocation which will have foreign key fields to store the ProjectID, DeptID, ResourceID, also needed will be a date field and a field to hold the value which I presume is your FTE
At the moment it looks to be something quite simple and should be straightforward for you to create. It's really about how you want it to work that will determine your form(s) design
David

 

[spikepl]

Some info is missing:

1. Who is going to use this , for what purpose and when, i.e. who inputs data, who changes data, who reads data
2. What is so unique about resource assignement in your organisation that you start off by wanting to design a db yourself, instead of looking for templates or checking the numerous web offerings.

 

[andy_dyer]

From what I can see from your sample.xls you'd require the following tables, some of the tables will data tables and some will be used purely as a lookup list to make a selection.
Project table with appropriate fields to store what project details you need
Dept table to store a list of dept's
Resources table to store a list of resource types
A main table which could be called something like ResourceAllocation which will have foreign key fields to store the ProjectID, DeptID, ResourceID, also needed will be a date field and a field to hold the value which I presume is your FTE
At the moment it looks to be something quite simple and should be straightforward for you to create. It's really about how you want it to work that will determine your form(s) design
David

Thanks David - yes that is exactly what I want!

I think most of my follow on questions would be form design specific... as in how do i get an entire period of time (likely a month) displayed on a form for me to then choose the dept and resource type and then put in the FTE usage per day...

Unless you have any suggestions I'll close this thread and start a new one in the Forms section?

Thanks so much for your help

 

[andy_dyer]

Some info is missing:

1. Who is going to use this , for what purpose and when, i.e. who inputs data, who changes data, who reads data
2. What is so unique about resource assignement in your organisation that you start off by wanting to design a db yourself, instead of looking for templates or checking the numerous web offerings.

Thanks for your response - this will be used purely by me - I'll need to gather the data from the project managers for their projects but the input, changing and reading will all be me.

There is nothing as i understand unique about my plans but I could find any template that i could simply adapt for my purpose - because my needs are arguably so simple I thought it might be quicker to build from scratch then try and work out another tool and adapt it.

Thanks for your input.

 

[DavidAtWork]

"as in how do i get an entire period of time (likely a month) displayed on a form for me to then choose the dept and resource type and then put in the FTE usage per day..."
Andy, how do you visualise this will look. If you only want to store data for the days you have an FTE value, then I would suggest each day would have to be entered separately, choosing project, dept, resource and date. These details could then be viewed using a sub form on the Project form for example.
Another option would be to design a table that would hold 31 sets of values, one set for each day of the month, but although the Project need only be selected once, you'd have to set the dept and resource for each of the 31 days, but you would be able to create a 2 dimensional monthly view a bit like a calendar.
David

 

[andy_dyer]

Thanks David,

I think it will need to be closer to the second option you mention there...

I may need to create a date table which stores month name i.e. February 2013 and then 28 dates and then March 2013 which stores 31 dates etc

Then I could choose Project A and then February 2013 and the two dimensional monthly view would open in a child form...

I was just trying to avoid creating a table with 365 records for the entire year if i could avoid it...

I have now opened a form specific question here:

http://www.access-programmers.co.uk/forums/showthread.php?t=240044

And I've thanked you for your help on this thread - if I've not frustrated you too much already and you have any suggestions on building the 2D form then i would continue to be very grateful.

Thanks so much

 

[jdraw]

Andy,

Take a look at this one for general info. May not be exact but seems to deal with the subjects -at least as a starting point.
http://www.databaseanswers.org/data_models/project_planning/index.htm

Good luck

 

[NJDevil]

Andy, Did you ever get this to work and can you please share your Access DB with us as an example? Can you please post it or send it to me via email? Thanks!