Auto generating fields

[Gambit79]

Hi All

I am trying to create a database to manage some project related functions. the main thing I want it to do is manage the resource allocation and costs related to fixed price projects and ultimately be able to produce reports that show the Bid vs Forecast vs Actual in relation to someones time and hence cost.

in building the first table which I want to store the initial Bid data. I have a need to have fields automatically generate based on the value of two other fields. this is used to identify how long the project will run for.
for example:
prject start date of 1/1/2014
project end date of 31/12/2014
Auto Generate 12 fields in which to put the monthly resource usage for each resource.

can someone tell me if this is possible.

thanks

 

[ButtonMoon]

Welcome to the forums,

Having a separate column for each month generally would not be a good database design because, among other things, it means any data access, query and reporting logic would have to be repeated for each column. Also, it's not typically a good idea to modify database table structures programmatically at runtime.

A database is not a spreadsheet. What I suggest you could do is create a ProjectResource table with columns for Project, Date, Hours, etc. Then you can assign resources and costs to individual dates as rows rather than columns. You can still present the information in a form with columns for months if you wish - the database design doesn't have to mirror what users see on screen.

 

[Gambit79]

Thanks ButtonMoon

I guess the problem is that I don't want a hundred rows in the database for monthly dates, I was thinking of setting a start date then an end date and having the months/rows generate based on those.
I have a spreadsheet at the moment and your right databases aren't spreadsheets, its more that I'm trying to pick the best design to end up containing the information the spreadsheet has. and then use forms / reports etc to pull out the info.

do you know of maybe some templates that might be close to what i'm after?

thanks for your help

 

[Cronk]

The advice you got from BMoon is correct. If you want a flat file structure use Excel. Are all the projects starting in January and finishing in December the same year?

In Access, you would have 2 tables, such as
tblProjects
ProjectID
ProjectName

tblResources
ResourceID
ProjectID
ResourceMonth
ResourceAmount

This way it is open ended as to when a project starts/ends as well as the number of months.

Also, having several years of projects in the same tables, you can easily query over variable time periods.