Relationships after the fact

[demax182]

Hi,

A little problem, I made a table (TblKid) that has a field Contract# and a field TaskOrder#. For each Contract#, there are many TaskOrder#'s. So now I want to create a new table (TblDad) with a field Contract# with additional info such as Contractor, Point of Contact, Phone Number, Address, etc... Then build a one to many relationship. However, 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, I'm sure of it! How do I get through this problem?!

Also, since there are many TaskOrder# to a Contract#, it is possible to have duplicate TaskOrder# in TblKid. However, the combination of TaskOrder# and Contract# is unique. I want to ensure that a duplicate TaskOrder# is not input within the same Contract#.

I hope my questions make sense, thanks for your help!

 

[willir]

demax182

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

 

[demax182]

Sorry, made it a bit confusing, let me use the real names of the tables. We have contracts here called IDIQ contracts usually with a number in this type of format: N62836-11235. Now IDIQ contracts have task orders. N62836-00001 can have Task Orders 1,2,3,4,5,6,etc... Now N62836-00002 can also have Task Orders 1,2,3,etc... I'm trying to make is ONLY one entry of N62836-00001 with Task Order 1. tblIDIQ and tblTaskOrder.

tblIDIQ has IDIQ Number as the Primary Key.

I can't use Task Order number for the primary key for tblTaskOrder since it is possible to have duplicate Task Orders. I can create another primary key with auto number, but that doesn't really solve the problem.

Thank you

 

[demax182]

I'm not trying to make a many to many relationship actually. For example, A1, A2, A3, A4, etc... B1, B2, B3, B4 etc... "A" is the contract number and "1" is the Task Order. Just want to make sure that the person does not input a duplicate task order for the same contract number. Damn, I'm confusing myself now, but I hope you get what I mean.

 

[PHV]

When in table design view for tblTaskOrder select the contract number and the Task Order fields and then click the PK button in the toolbars.
Now you have a composite Primary Key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244