Unique records based on 2 fields

[demax182]

This may be simple or this may not be possible to do. Here's an example:

I have a field called [Letter] and a field called [Number] in one table. I want to ensure that for every [Letter], there's only one [Number] and vice versa. The user may do [Letter] = A and [Number] = 1 for one record (A1). I want to prevent the user from inputting the exact combonation of number and letter again. Is this possible?

Thanks in advance,

Mike

 

[PHV]

In the table design window, create a unique composite index on this 2 fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[demax182]

Forgive my lack of knowledge, but how do I go about doing that?

 

[Edski]

I want to ensure that for every [Letter], there's only one [Number] and vice versa.

So if there's a record with A1, no other record can have Letter=A or Number=1 (following your statement above). That is, B1 is not allowed, nor is A2.

The user may do [Letter] = A and [Number] = 1 for one record (A1). I want to prevent the user from inputting the exact combonation of number and letter again.

This contradicts your above statement. An EXACT combination of Letter and Number means if A1 exists, A2 is allowed, and so is B1, because they noth are not EXACTLY the same combination as A1.

So which do you mean?

If the latter, then do as PHV says. Or make both fields the primary key in the table (highlight both fields and cick on the key button).

 

[Edski]

Sorry for the typos. It should have been: because they both are not EXACTLY...

and ...click on the key button.

Next time I'll proff-read before posting.

 

[demax182]

Ok, maybe I'm confusing myself with my wording. What you said in the latter is what I want to do. If there's an A1, then a B1, C1, D1, etc... would be feasible still. And if there's an A1, then A2, A3, A4, and etc... would be feasible as well. If there's only one letter to a number, that would mean A1 is the only thing you can have. '1' is the only number for 'A' thus A2, A3, A4 is not feasible, which is not what I meant. On my part, I'll word my questions more carefully next time I post. Thanks.

Mike

 

[PHV]

but how do I go about doing that
When in table design view, ask the help wizard for unique index

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244