As I recall, in Access, by creating a unique index on the combination of
fields (essentially a secondary key), you will enforce uniqueness, but allow
nulls. Another way to handle the optional nature of the document
title/revision combination is to split that into its own document identity
table, leaving the other fields in a document content table, and have a 1-to-1
relationship between them with the document content record as the source for
the primary key. The document identity table would use a uniqu index to
enforce uniqueness of the title/revision combination, but both fields would be
required since the record itself id optional.
To create a unique index on a combination of fields, you must go into the
indexes popup in table design, and put the 2 fields on consecutive rows with
the index name only on the first row. With the first row selected, check the
"Unique" box in the index properties.
On Tue, 14 Dec 2004 14:58:18 +0400, "Burghew" <bu*****@mailinator.com> wrote:
Hi All,
I have 2 fields called document & Revision which contains the titles for
the documents to be stored in the databse it gets filled up as the user
enters them and the revison numbers associated with them.
1. What I need to do is to make sure that no duplicate entry is entered for
document and if entered should have a different revison number. I cannot
make them both as the primary key bacause there may be times when both the
fields maybe null but the entry must be there in the database.
2. If the user types in an existing document then I need the revision field
to be filled up with the current revision number after update.
I am a bit stuck here and any help will be great.
