Validation rule, index or neither?

[Zippy]

I have a fairly simple table which has three fields.

Field1 is a Unique ID
Field2 is a Part number ie P0001
Field3 is a test number ie Test 1

What I am trying to achieve is to have the unique ID which is not allowed to
have duplicates and Fields 2 and 3 to be unique in combination
For instance:

UniqueID1 P0001 Test 1
UniqueID2 P0001 Test 2
UniqueID3 P0002 Test1

Is fine but:

UniqueID1 P0001 Test 1
UniqueID2 P0001 Test 2
UniqueID3 P0001 Test1

is not.

I thoughta multifield index would work but I can't get it to work with the
UNiqueID field as part of the index. Should I be using another technique
such as a validation rule or am I completely on the wrong track?

TIA

Zippy

 

[Jeff Boyce]

What do you mean when you say "can't get it to work"? How are you
attempting to set that multi-field index?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Douglas J. Steele]

Set the PrimaryKey equal to Field1 and create a unique index on the
combination of Field2 and Field3. Field1 should not be part of that second
index.

 

[Zippy]

That is what I've done I believe.
UniqueID has the "Unique" property set with Field2 and Field3 also in the
index - but this then allows what I showed in my second example which is
what I don't want.

Zippy

 

[Zippy]

So you're saying Field1 should be independent of the other two fields. So I
should set the primary key in the table to Field1. Then in the multifield
(which will have Field2 and Field3) have the "Unique" identifier against the
index name.

I think I've got that right - if I've missed something blindingly obvious
then please berate me.

Zippy

 

[Douglas J. Steele]

Yes, that's what I'm saying.

Did it work when you tried it?

 

[Zippy]

I've just set up a small test version similar to the actual database which
I've got at work and as far as I can tell it does exactly what I was trying
to achieve.
Thank you very much for your help

Zippy