demax182
SoFar said:
TblKid
Contract#
TaskOrder#
Contract# (1) : TaskOrder# (M)
TblDad
Contract#
Hmmmm.
This sounds familiar to a support database I use...
A job is awarded to a contracter.
There can be many tasks to accomplish
Possibly, another contractor may do some of the tasks.
(Consider dropping the "#" -- the number sign / pound sign / octophorp is a psecial character -- used for "encapsulating dates -- See
FAQ 700-2190 - Avoid space characters in any 'Name' - Why ?)
Consider the following design...
tblContractor
ContractorID - primary key
ContractorName
... etc, info unique for each contractor
tblContract
ContractID - primary key
ContractTitle
ContractStartDate
ContractEndDate
ContractorID - foreign key to tblContractor
... etc, info unique to the contract
Discussion:
One contractor is responsible for the entire contract
tblTask
TaskID - primary key
ContractID - foreign key to tblContract
TaskTitle
ContractorID - foreign key to tblContractor
TaskStartDate
TaskEndDate
HoursOnTask
... etc, info unique to the task
Discussion:
The contractor doing the job can be the same as the contractor who owns the contract, or it can be different, i.e., a subcontractor.
You can take this further, especially if you want to get into itemized billing.
tblMaterialsUsed
MaterialsUsedID - primary key
ItemCode - foreign key to Item table (not shown)
TaskID - foreign key to tblTask
ItemQuantity
Discussion:
Captures info on material used to perform a task.
tblEquipmentUsed
EquipmentUsedID - primary key
EquipmentCode - foreign key to equipment table (not shown)
EquipmentUsageHours
Discussion:
Captures info on (special) equipment used to perform a task.
...Getting back to your questions.
How do I get through this problem
Contracts and tasks are related.
Contractors and Contracts are related.
But treat the contractor and tasks separately. Focus on each relationship.
I am not sure why you have Kid and Dad as your table names -- gets confusing from my perspective. Hopefully, the above review and design are in line with what you are looking for.
when I try to build the relationship, it doesn't let me ... All the records I have in the secondary table are within the primary table
If you have added test data, the data itself may be preventing you from creating a relationship. Or you may be using different data types. If this applies to you -- Another reason can be if you are creatnig relationships on a front end database -- relationships should be created on the back end, and not on the linked tables.
Usually, you create the design and relationships first. Then test the design with test data. Then create your forms and reports and test again. Then blow away your test data before pusing the database into production.
since there are many TaskOrder# to a Contract#, it is possible to have duplicate TaskOrder# in TblKid ... TaskOrder# and Contract# is unique
Hmmm. My lights went on just now.
Firstly, you seem to be describing a many-to-many relationship. Secondly, where I finally clued in, I suspect you are talking about "kids" being assigned to a task. And is "Dad" a person in charge (or not in charge ;-) ) of the kids?? I think the above design will work, with a tweak...
tblTask
TaskID - primary key
ContractID - foreign key to tblContract
TaskTitle
ContractorID - foreign key to tblContractor
TaskStartDate
TaskEndDate
HoursOnTask
Discussion:
ContractID would be the "Dad"
tblContractor
ContractorID - primary key
ContractorName
ContractorType - "Dad", "Kid"
tblTaskWorkers
TaskID - foreign key to tblTask
ContractorID - foreign key to tblContractor
DateWorked
HoursWorked
Primary key = TaskID + ContractorID
The above uses a many-to-many relationship for workers and a task. A task can have many workers, and a worker may work on many tasks. Instead of "worker", I Used Contractor for consistancy with the above design.
You can assign and track who worked on what and when.
You will know who is responsible for the task and for the contract.
Hopefully, my idea can be bent to your needs.
Richard
