Create tables from another table

[JoeyJ]

Hi All

I am trying to create a database for estimating manhours on projects. There is a setup table with two columns: "Project" and "Time Period". The database is supposed to allocate manhours to tasks on projects, which is used in estimating man hour costs and also forecasting labour resource requirements.

For example, say the database is used for "Project X" and "Project Y" projects, with Project X running from Jan to March and Project Y running from Aug to Dec. The setup table would look like this:

PROJECT TIME PERIOD
Project X Jan
Project X Feb
Project X March
Project Y Aug
Project Y Sept
Project Y Oct
Project Y Nov
Project Y Dec

From this table, I need to automatically create a new table for each unique project that allows man power to be allocated to each time period. Following on with the example, there are two unique projects, so two tables
need to be automatically created with column headings as per below:

Project X table:
FUNCTION PERSON JAN FEB MARCH

Project Y table:
FUNCTION PERSON AUG SEPT OCT NOV DEC

"Function" is a description of a role, for example engineer. "Person" is the individual undertaking that role, for example Bob Jane. Then in each time period a number between 0 and 1 would be entered corresponding to how much time (0 is no time, 1 is full time) that person would be spending on that function for that time period. Then the table would be populated with many functions and people in this manner.

So the number of tables automatically created depends on how many unique projects there are in the "Project" field of the setup table. The number of columns in these tables will be atleast two: "Function" and "Person",
plus another column for each time period defined for that project.

Perhaps something can be done with a make-table query, but all I can get that to do is copy data from one table straight into another table.

Any help would be greatly appreciated.

Thankyou in advance.

Joey

 

[neileg]

You need to find out about normalisation.

You don't need multiple tables, or multiple columns. You need one table that identifies the project, the function and the person. You will also, in each record, identify which time period and the time allocation. This way you have one record for each person/project/time period, not one field.

 

[JoeyJ]

Thanks for your suggestion neileg.

I think I understand what you are saying, but I think it would be impractical. One person will be working over many time periods, perhaps 20 or so, using your method would mean creating a new entry for every time period and selecting that same person for every entry. Also, there will be many different people over a long time period. Using your method, you would be repeatedly entering in the same time period for each different person, and this would be repeated for the many different time periods. Also, there could be any number of projects, not just one.

If I have interpreted your idea incorrectly could you provide more details? Anyone else have any ideas?

 

[neileg]

You are thinking about this as a series of spreadsheets. That's not how a relational database works. The way you view the data is completely independant from the way you store it.

I don't understand how there can be more data entry in one design than the other? Every person has the same number of projects and timeperiods and you need to record each one.

 

[JoeyJ]

For example, with my method a typical project would look like this:

Person Function Jan Feb Mar Ap Ma Jun Jul Aug
Robert Accounts 0.5 1.0 1.0 1.0 1.0 1.0 1.0 1.0
Jennifer Engineer 0.3 0.5 0.5 0.5 0.5 0.5 0.5 0.3

Using the other method, It would look like this:
Person Function Time Utilisation
Robert Accounts Jan 0.5
Robert Accounts Feb 1.0
Robert Accounts Mar 1.0
Robert Accounts Apr 1.0
Robert Accounts May 1.0
Robert Accounts Jun 1.0
Robert Accounts July 1.0
Robert Accounts Aug 1.0
Jennifer Engineer Jan 0.3
Jennifer Engineer Feb 0.5
Jennifer Engineer Mar 0.5
Jennifer Engineer Apr 0.5
Jennifer Engineer May 0.5
Jennifer Engineer Jun 0.5
Jennifer Engineer Jul 0.5
Jennifer Engineer Aug 0.3

 

[neileg]

You're looking at this in spreadsheet terms, again. There's nothing to stop you creating a data entry form where the worker name stays the same until you change it and if you want a grid type of entry, you can do this too.

You need to get the basic design correct before you design the forms. I know it can be hard to 'unlearn' the techniques you would use if this was set up in Excel, but you need to realise that Excel and Acces just don't work the same way. If you want to use an Excel approach, use Excel.

 

[JoeyJ]

Thanks for your response Neil

Your posts have helped me understand normalization and good programming principles.

My main concern is the database needs to be quick to interpret and modify, as it will be used on a day to day basis and so needs to be as fast as possible to use. Doing it the correct way, apart from being tedious during the initial data entry (not a huge problem), would make it harder to look at the data as a whole and interpret or modify it.

What do you mean by "grid type entry"? I don't know what that is but it sounds like it might be what I need to do.

 

[JoeyJ]

Thanks for that Pat.

When I said it would be harder to look at the data as a whole, what I meant was you can't present it in a nice way AND be able to edit it at the same time. I know you could do some cross tab queries and reports to make it look like what I want, but you need to go back to the original table to modify the data. My incorrect way would present the data in a nice way and be editable at the same time.

But that database you pointed me to looks like it may do the trick. One problem I see is the projects could go for a long time and could start in a few years time, which would mean I need to pivot a few years worth of months or something like that. I will check out that database when I get home, the company I work for still uses good old Access 97.

 

[raskew]

Pat -

Would you be kind enough to post an A97 version of your example.

Thanks,

Bob

 

[raskew]

Pat

It converted perfectly. No reference glitches. Just did a compile/save and it was off to the races. Have posted the A97 version as an attachment to your samples posting.

Thanks for your help.

Bob

 

[JoeyJ]

Thanks for your help guys.

It all got too hard for me, I am just gonna stick with the original advice - normalise the data and leave it at that. It wont take that much longer to enter that data, but it makes everything else much more simple.