not allow for duplicate for a combination of two fields

[babs]

Presently I have clientid and ticket# set as both as primary keys. Together
they can not be duplicated - so I made them both primary. However I am
having other trouble with relationships between other tables and need to add
a ticketID autonumber that would be unique for each record as the Primary
key.

How can I set Properties??? for the clientid and ticket# that they need to
be in combination UNIQUE for the user inputting the data???

Thanks,
Barb

 

[KARL DEWEY]

If they are in the same table you can setup a compound index and set property
to unique.
In the design view of the table click on menu VIEW - Indexes.

You can then create the index using the fields you want and set unique.

 

[TC]

You can not have two primary keys. What you probably have, is a
so-called "composite" primary key. A composite primary key is a primary
key comprising of more than one field. This is common for the child
table in a one-to-many relationship.

HTH,
TC

 

[babs]

Sorry so long. Just got to this.

I did what you said but the clientid on its own is not unique - it is the
combination of the clientid and the ticket# together that need to be unique.
Would rather not choose both keys as a composite(guess the right word)
primary keys. Would like to have a new autonumber be the primary key.

Any ideas???

thanks for your help
Barb

 

[Rick Brandt]

Sorry so long. Just got to this.

I did what you said but the clientid on its own is not unique - it is
the combination of the clientid and the ticket# together that need to
be unique. Would rather not choose both keys as a composite(guess the
right word) primary keys. Would like to have a new autonumber be the
primary key.

Karl did not suggest a compound Primary Key. He suggested a composite INDEX
with its unique property set to yes. Read it again.

 

[babs]

Sorry if I was unclear. I went to the view and indexes in the design view of
the table and for the seperate fields - client id and ticket # set them to
unique- yes. This is not what I want because only as a combo they need to be
unique. Guess there is one step prior to make these two fields composite or
compound not sure what that means or how to do that?

Please help.

Thanks for your input,
Barb

 

[Rick Brandt]

Sorry if I was unclear. I went to the view and indexes in the design
view of the table and for the seperate fields - client id and ticket
# set them to unique- yes. This is not what I want because only as a
combo they need to be unique. Guess there is one step prior to make
these two fields composite or compound not sure what that means or
how to do that?

In the indexes dialog enter thusly

NameOfIndex FieldName1
FieldName2

(at the bottom)
Unique = True

For an index consisting of more than one field you enter the name of the index
only on the first row. In subsequent rows you enter only field names.