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
