Multiple contract dates

[Guest]

I am creating a database to track our vendors. Each vendor has a contract
which is renewed at various times (annually, bi-annually, etc.). I would like
to keep the start and end date of each contract, but do not want to enter all
the vendor's information each time there is a new contract. What is the best
way to set the table/form up to capture the contract start and end date each
time we renew?

 

[John Vinson]

I am creating a database to track our vendors. Each vendor has a contract
which is renewed at various times (annually, bi-annually, etc.). I would like
to keep the start and end date of each contract, but do not want to enter all
the vendor's information each time there is a new contract. What is the best
way to set the table/form up to capture the contract start and end date each
time we renew?

You probably need three tables: Vendors; Contracts (if a vendor can
only have one contract then these can be combined into one table); and
Renewals. Assuming that you do combine vendors and contracts into one
table, your Renewals table would have fields for the VendorID (or the
Contract Number, whichever is appropriate in your case), as a link to
the Vendor table; start and end dates; and any other information about
this renewal.

You'ld have only one record for vendor information, but as many
records in the Renewals table as needed. To enter the data into this
table, you'll find a Form (based on Vendors) with a Subform (based on
Renewals) to be convenient.

John W. Vinson[MVP]

 

[Debra Farnham]

Hi Misty

I would create at least two tables (in the absence of any further details
such as does one vendor have more than one contact person or address?)

1. Vendor Information
2. Contract Details

tblVendors:

lngVendorID (PK)
strVendorName
strVendorAddress
etc etc

tblContractDetails

lngContractID (PK - perhaps an autonumber)
lngVendorID (FK - must be same datatype as VendorID in tblVendors)
dtmStartDate
dtmEndDate
any other fields related to the details of the contract

You can then create a relationship between the two tables and build any
necessary queries and forms without having to enter Vendor details more than
once.

For example, you can use a form with a subform to display the vendor
information and corresponding contract details or a single form with a
listbox that shows contract details based on the selection of a vendor from
a listbox or combobox.

There are numerous ways of handling the form situation, depending on whether
or not you are using the form to view the information or update it or both

HTH

Debra

 

[Guest]

Hi Misty,

You need to store the contract information in a separate table, which is
related to the vendors table in a one-to-many (1:M) relationship. The vendors
table would be the parent table (the "one" side of the relationship), and the
contract information table would hold zero to many related records for each
vendor. You may even want to add a third table, if the number of contract
types is relatively fixed.

You can display this information on a form using the form/subform setup. The
main part of the form would display vendor information (name, address, city,
phone, etc.) while the contract information would be displayed in a subform
in datasheet view.

Here is a suggested setup for a three-table design:

tblVendors
pkVendorID Autonumber primary key
VendName
VendContact
VendAddress
+ other attributes related specifically to the vendor


tblContractTypes
pkContractID Autonumber primary key
ContractType
+ other attributes related specifically to the contract


tblVendContracts
pkVendContID Autonumber primary key
fkVendorID Long Integer foreign key to tblVendors
fkContractID Long Integer foreign key to tblContracts
StartDate
EndDate

Using the Tools > Relationships menu, you would create relationships between
the primary and foreign keys with similar names, ie:

pkVendorID ---> fkVendorID (1:M) and
pkContractID---> fkContractID (1:M)

The tblVendContracts table serves as a "linking" (aka "intersection" or
"join") table. This is how you model a many-to-many (M:N) relationship
between vendors and ContractTypes.

To help prevent duplicate entries, you can create a multi-field unique index
on four fields in this last table: fkVendorID, fkContractID, StartDate and
EndDate. Of course, that would mean that you could not have two contracts of
the same type with the same vendor that had identical starting and ending
dates.

Here is more information that you might find useful on database design:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I am creating a database to track our vendors. Each vendor has a contract
which is renewed at various times (annually, bi-annually, etc.). I would like
to keep the start and end date of each contract, but do not want to enter all
the vendor's information each time there is a new contract. What is the best
way to set the table/form up to capture the contract start and end date each
time we renew?