Structure/Form consideration advice

[Adam]

Hello,

I am creating a table which has the following fields:
- FinancialID (Autonumber PK)
- Date
- Market
- Measure
- Type
- DateModified

I want to ensure that between Date,Market,Measure & Type fields that
there are no duplicates.

I have considered adding a field 'IntegrityKey' and consolidating the
above 4 fields into one and then Indexing with No Duplicates.

Two questions with this solution:
1. Does this sound like a solid sustainable way to go?
2. How would one implement this in a form, as the field needs to be
populated from the other 4 fields, which does not seem to happen when
I put this in a simple Autoformat form.

Appreciate all of your help and advice!

Adam

 

[Beetle]

First, don't use the words Date or Type as field names, as they
are reserved words and will cause you problems. For a more
complete list of reserved words see;

http://www.allenbrowne.com/AppIssueBadWord.html



Second, don't try to use some type of surrogate, concatenated field for
this.

All you need to do is create a unique index on those fields. Open your

table in design view, go to View/Indexes. Your PK field should already

be listed there. In the first blank row put an Index Name in the first
column,

then select your first field in the second column, then set Unique to Yes

in the Index properties. On the next three rows, leave the Index Name blank

and select the next three fields in the second column. These four fields
will

all be part of that same unique index.



__________



Sean Bailey

 

[Adam]

Ok, in doing this will it ensure that date can be duplicated, as can
other of the other 4 fields, however all 4 fields together cannot be
the same as seen in any sequence in the table prior?

 

[Adam]

Just tried it and it works.

Thats brilliant - thank you for helping me on this, I'll use this
quite a bit now.