Primary Key/Validation/Query

[rtb3001]

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

 

[gemma-the-husky]

just make the primary key multi field. Order your key fields sequentially, highlight all of them, and set PK. MAKE SURE there are NO NULLS in the data, as access will not respect nulls, and will duplicate keys with niulls in them

 

[Pat Hartman]

Access will not allow you to create a primary key if any fields contain nulls or would cause duplicate values. You may need to clean up the data first.

 

[gemma-the-husky]

thanks Pat,

I had the duplicate keys problem with a segmented unique secondary key (or so i thought) which wasnt unique because Access doesnt respect the uniqueness of secondary keys with nulls in them.

 

[Pat Hartman]

If you have secondary keys that may be null, you have not properly normalized your tables.

 

[gemma-the-husky]

You could be right, Pat. Effectively, I was trying to store a bulk quantity order with a supply date. If no date was specified, supplies could be drawn offf on any date. If there was a date, then the order could only be supplied on that date. Perhaps I could have included a further table somehow, or on reflection included an extra yes/no fierld for this. I thought having a NULL date would be OK

Anyway, I included the date in what I thought was a unique key, to try to ensure that there could be no duplication of order lines, but later fond that keys which included NULL for the date in that part of the segment were duplicated. There is an Access Key setting "Ignore Nulls", but this had no effect whether set to true or false, so I don't know what iti s supposed to do.

I finally resolved the issue by setting the date to 0, rather than NULL. But because 0 resolves as a true date, I had to modifying the code to specificaly test for 0.


I still don't think it is unreasonably for NULL to be a valid value in part of a key. I've done a lot of work on VMS, and there was no problem there with blank fields (although they are treated as spaces, not NULL)

 

[Pat Hartman]

The reason that null is not valid as part of a primary key is because you cannot join on it. If you try to join one null value to another, you will not get a match. Just like you can't ever get a true result when comparing two fields that are null. a = a returns false when a is null.