Linking Tables - Can I copy common data?

[PATRICKPBME]

I have over 500 contracts with the primary key being the contract number. While not needed previously, I now have been asked to create a table showing different payments made under each contract. Some may have only 1 type of payment and others as many as 5. My payment table will have a row for each contract number and a column for each payment type. The common key in each table will be the contract number.

I don't want to have to type each contract number into the new "payments" table. If I cut and paste and then enforce referential integrity (assuming I can) will:

1. Changes in data to 1 table make changes to the other?, and;


2. If I add a new contract to one table will it also be added to the other table?

Thanks in advance!

[jwhite]

Instead of having repetitive columns for the same entity (payment), each payment type should have its own record.

tblContracts
...ContractID

tblContractPayments
...ContractPaymentID
...ContractID
...ContractPaymentTypeID

tblContractPaymentTypes
...ContractPaymentTypeID
...ContractPaymentType

[PATRICKPBME]

I may not have been clear or (more likely) am not adept enough to understand your suggestion. There will not be multiple payments for each entity. The payment types and amounts will always remain the same. This is just a report that will show what rate is to be paid under each contract. In other words, contract #1 would always be paid, for example, based on the rate in columns 2 and 3. Someone else will be calculating the final payment amount based upon the rates I provide.