Table design question 2

[TCARPENTER]

I think maybe I need a reality check but anyway:

I have a database with 70 some tables in it and now need to do costing involving roughly 5 to 6 tables. I have created to new tables:

tblCosts
[tt]Recnum PK
Location_Recnum FK - Location/Placement of item
Item_Recnum FK - Items to place
Trade_Recnum FK - Trades needed (carpenters, painters etc)
TradeHrs Estimated hours
TradePeople Number of tradespeople
ItemCost Cost per item
ItemQty Number of items[/tt]

and

tblTrades
[tt]Recnum Index
Name PK- Trade name (carpenter, painter etc)
HrlyRate Hourly rate[/tt]

My tblCosts table is a join table and this may be where I've gone wrong. The Item_Recnum pulls its information from 5 different tables, which I have linked to this field. After I entered some data, Access barked at me telling me I needed to enter a non-null value in one of the other linked tables. I'm assuming I goofed linking the 5 tables to this one field? Each of the 5 tables I'm pulling information from (via a union query), has a join table associated with it for locations, should I have related the join tables to my cost table, or should I have added an additional field to my 5 other join tables pointing to my costing table?

Any help would be greatly appreciated!

Thanks
Todd

 

[willir]

Hi Todd

I have seen past posts and this "project" look awsome.

Before I try to answer your query, can you provide some info...

Location_Recnum FK - Location/Placement of item
What is this exactly?

Item_Recnum FK - Items to place
And this is??

I am guessing that tblCosts referes to tasks such as install stairs, put in drywall etc. So let's sya you are building houses...

House1 - 2800 sqft
- put in deluxe stairs - requires 4 trades, 6 hrs
- drywall - requires 6 trades, 18 hrs

House2 - 1300 sqft
- put in stairs - requires 2 trades, 4 hrs
- drywall - requires 4 trades, 6 hrs

...type of thing.

Next, if the above assumption is correct, can a task involve more than one type of trades person??

Do you have or need an Item table?

Last, for now, you have Item_Recnum linked to five tables. What are these tables?

Richard

 

[TCARPENTER]

Hi Richard,

Thanks for the reply and the compliment!

Location_Recnum refers to a location within a plant, or more exactly an actual workstation within a plant. This comes from a join table which houses all the workstation locations, what they contain, product etc.

Item_Recnum comes from 5 different tables: tblSpecialTools, tblTools, tblPlatforms, tblRacks, and a catch-all table called tblVals which is another join table consisting of tblGroups, and tblFields. This houses mainly miscellaneous items (workbenches, chairs, electrical drops, air drops etc.).

Your assumption of tblCosts referring to installation tasks and costs is exactly right, and yes many times an item does involve several different trades, i.e., a platform may require carpenters, painters and sometimes electricians.

I do not have an Item table per se, other than the 5 tables mentioned above - I did earlier on use a union query linked to the Items_Recnum field in the tblCosts table. But I found I couldn't enforce integrity, cascade updates etc., which made me think my costing table would need to be manually updated everytime something within a workstation changed? So now my union query resides in a forms combobox's row source property. Since this thing is such a beast and incredibly complex already, I did'nt want to add anymore work than was absolutely necessary for the users.

Hope that answered your questions well enough, if not, let me know and I'll try again.

I'm off to a meeting and won't be back until tomorrow but I'll check in first thing in the morning.

Thanks again
Todd

 

[willir]

Thanks Todd - I will get back to you tonight

 

[willir]

Okay Todd
(This thing is better discussed over a beer or two, but what the heck)

I need a little more info, but here is a stab at what I worked out this evening...

You have a Project.

The Project consists of a group of tasks - you use a Cost table to track this.

A task is complicated. It can consist of...
- special tools
- regular tools
- misc items
- racks
- platforms
- trades people
- materials

You have two issues...
- Retrieve the data to effectively caculate costs (you current achieve this via a Union query)
- Something changes - a name, a source, etc that creates referential integrity issues.

A lot of this is a shot in the dark, but hopefully, I am close on parts of this because it what I am basing my assumptions on.

First, use a hidden field for your primary key. For example, use Autonumbers (sequential numbers) or ReplicationID and let the system maintain integrity. Since the end users and you are not really interested in these numbers, and they are not tied to a specific text or numeric code (such as work order or requisition numbers), they wont be changed, and cascading updates are not an issue. A sidetrack... Using a general ledger example, a common approach is to use the G/L account as the primary key - makes sense until you change the account number - now all transactions have to be renumbered. In, the G/L account number is just a description, perhaps used for sorting. "Behind the scene", a system number is used to link the G/L chart to the transactions - changing a G/L account number just involves changing the number on the G/L master file; the transactions no longer have to be renumbered. (Sorry for the side track, but just wanted to present this scenary so you hopefully understand)

...Moving on to the design.

I think your design would benefit by defining specific tasks. Then instead of linking one of five tables to the cost table with a union table, you only have to link the task. Still, defining the tasks may be awkward.

The basics

tblJob
JobID - PK
JobDescription
LocationID - FK
StartDate
EndDate

tblTrades
TradeID - PK
TradeDesc
TradeRate

tblMaterial
MaterialID
MaterialName
MaterialCost
MaterialUnits
MaterialSpecifications

tblCost
CostID - PK
JobID - FK
TaskID - FK
TaskUnits
StartDate
EndDate


You have a many-to-many relationship between material and the cost table. A "cost" can have more than one material, and material can be used for more than one "cost". A "Joiner" table is used to handle this M:M relationship...

tblMaterialCost
CostID - FK
MaterialID - FK
MaterialQty
- primary key is MaterialID + CostID
- Use a subform basded on this table. A combo box is used to select the material for a "cost" item, and then enter quantity. This silution will also allow you to calculate how much of each material was used, and used for which project.


Now comes the toughest part. I am not entirely happy - about this solution - way too complicated, but it should work.

tblTask
TaskID - PK
TaskDecription

...Plus Many-to-Many tables for each of the (resources) tables in question. When I worked things through several variations, I realized that you may or may not have this or that, and you may have more than one of this. So you would need joining tables to establish the relationships. Using "joiner" tables also allows you to create records only where needed.

tblTrade
TradeID - PK
TradeName
TradeRate

tblTaskTrade
TaskID - FK
TradeID - FK
TradeManHrs - total man hours needed
MinMan - minimum number of workers
MaxMan - maximum number of workers (too crowded type of thing)

tblSpecialTools
SpecialToolID - PK
SpecialToolDesc
SpecialToolRate

tblTaskSpecial
TaskID - FK
SpecialToolID - FK
SpecialToolUnits

tblTools
ToolID - PK
ToolDesc
ToolRate

tblTaskToolsl
TaskID - FK
ToolID - FK
ToolUnits

Repeat this basic structure for Racks, Platforms and Vals


Why have a Cost table and a Task table - the task and cost tables are similar?
By predefining your tasks, when preparing costs, you can select prepared tasks instead of calculating ask for each new job. (Whoops, forgot I need these trades and those trades for the task)

How it works...
Once you have worked with "joiner" tables a few times, it becomes fairly easy. You create a contineous form based on the joiner table. The form will be used as a subform embedded in a master form.

For example, assigning trades to a task.

Create a contineous subform based on TaskTrades has the TaskID and TradeID. You hide the TaskID and convert the TradeID to a combo box to "hit" the Trades table. When you drag and drop the subform onto the Task form, use the TaskID to link the forms. After you create your task, use the subform to assign each trade as appropriate, and the appropriate number of man / work hours and worker for the task.

Ditto for each of the other entities used to create the specific task.

Hint: A tab subform would work really well with this design. Each tab would have the a subform for the joiner tables for Trades, Special tools, tools, racks, platforms and vals.

Hint: You can take each of the joiner tables, hide the resource ID and make the TaskID visible. Embed this modified contineous subform into the appropriate resource form. Now you can see which task used the specific resources. With minimal effort, you have doubled the information provided to management.

Where does your Cost table come in?
Although you have created each task, you still need to assign resources. You may want to double or tripple the workers to speed up the work, or double or tripple the other resources because of the size of the specific task.

Later, you decide upon a better way of accomplishing a task. Create a NEW task and assign it to future jobs. Old jobs will still being up the tasks as originally designed.

Why have a Job table?
It allows you to break up costs / tasks for various parts of the project. Having a Cost table and Job table also allows you to track management over all and for specific tasks.

Why do I not have warm and fuzzies?
The numerous M:M add complexity. For example, to calculate the total costs is do-able but may require you to run several queires - one for each resource.

Which leads me to one last thing. Costs. You will change costs for each resource over time. For historical pruposes, costs for past projects will pick up the new costs. Therefore, you may want to store final costs on the Cost table (of course). The costs will be based on the rates set in the resource tables, but it is understood that these resource rates will change. This may also make it easier for calcuating total costs for the project.

I realize that you are probably quite far into your project, and may be reluctant to change. However, hopefully some of the stuff presented is useful.

Richard

 

[TCARPENTER]

Richard,

For a shot in the dark that was one well thought out reply! Thank you so much for all the effort - stars to you!

After looking over this and digesting it a bit, that's pretty much what I was thinking I would have to do, although I won't have to define the tasks, the material, or items, really defines the task itself. They are only interested in hours, and dollars per hour (for now).

Many of the join tables you mentioned for special tools, tools, racks, and platorms already exist, so it should just be a matter of adding in the required fields.

Thanks again for the great reply!
Todd