Help designing Tables

[Gary]

I have a Fee Table with Contract #, Company Name, and many
$ fees for services (no primary key)....I have a Service
Code table with many services by code (both text code &
Number code) Autonumber primary key....
I have a form with combo boxes to select Service codes by
catagory - Now I want to match up the Service code
selected in each combo box by the Fee which is different
per Contract # and Company Name and produce a budget
report or form - this may be a many to many relationship ?

 

[John Vinson]

I have a Fee Table with Contract #, Company Name, and many
$ fees for services (no primary key)....I have a Service
Code table with many services by code (both text code &
Number code) Autonumber primary key....
I have a form with combo boxes to select Service codes by
catagory - Now I want to match up the Service code
selected in each combo box by the Fee which is different
per Contract # and Company Name and produce a budget
report or form - this may be a many to many relationship ?

If a given Contract can have many fees, and a given Fee can apply to
many contracts, then yes. The proper design would be:

Contracts
ContractNo Primary Key (don't use # in fieldnames, it's a date
delimiter)
<other contract specific fields>

Fees
FeeID Autonumber Primary Key
FeeType

ServiceFees
FeeID Long Integer <link to Fees>
ServiceCode Long Integer <link to numeric Service Code>
FeeAmount Currency

ContractFees
ContractNo <link to Contracts>
FeeID <link to ServiceFees>
ServiceCode <link to ServiceFees>

 

[Gary]

Do I need to create a Service Code Table also? (link to
numeric Service Code) in your plan ??????

 

[John Vinson]

Do I need to create a Service Code Table also? (link to
numeric Service Code) in your plan ??????

Yes.