Primary Key/Validation/Query

Hi everyone,

Hopefully someone can help me.

In Table 1 - I have a set of contracts that have a unique number. However, although the primary key is the contract number - these numbers can be duplicated as long as their start and end dates don't overlap. So my question is, how do I make the Primary key the contract number plus the start and end date? Is this possible?

I know I can select multiple fields for the primary key, but this has a problem...

Here are 3 examples of how Table 1 could be constructed and the Table outline...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Records
Contract Number/Contract Start/Contract End/Contract Details


001/01-Apr-07/31-May-07/Items Cost £1
001/01-Jun-07/31-Jul-07/Items Cost £2
001/01-May-07/30-Jun-07/Items Cost £1

Now, if I select Contract Number/Contract Start/Contract End as the primary fields, the above would not create any conflicts. However, the third record overlaps the first two records date barriers - and I don't want that to be possible.

Any new record's start date MUST be after any existing record with the same Contract Number's End Date. Also, any new record's end date MUST be before any existing record with the same Contract Number's Start Date.

In Table 2 - I have a list of orders that relate to Table 1. i.e. Each order has a contract it relates to. How do I make sure that in a query, the order number picks up the correct contract for the corresponding date.

For example...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Table 2
Field 1: Order Number
Field 2: Order Date
Field 3: Contract Number

My query would pick up Order Number, Order Date, Contract Number, Contract Details.

I think what I'm looking for is validations in the fields. But I'm not entirely sure how to implement those.

I hope I'm making sense...

Thanks again for any assistance,

Ross