Ensure Uniqueness without Keying?

[Douglas J. Steele]

Why not just create a unique index on the 6 fields? You can include up to 9
fields in a single index.

 

[Daryl G.]

Is there a way to ensure the uniqueness of a record without stringing all
the fields together as a key?

For instance I have the following table:

Code (Autonumber) (Key)
Address1
Address2
City
State
Zip

Now for any give field, the data may be duplicated. Ie. 25 Smith Street may
be valid Address 1 field 50 times provided the address 2 or some other field
is empty

But the entire string should never be allowed to duplicate

Valid Data:
Code | Address1 | Address2| City | Sate| Zip|
1 | 25 Smith Street | | Somewhere|NH | 11111
2 |25 Smith Street | Suite 200 | Somewhere|NH | 11111

Invalid Data:
Code | Address1 | Address2| City | Sate| Zip|
1 | 25 Smith Street | | Somewhere|NH | 11111
2 |25 Smith Street | Suite 200 | Somewhere|NH | 11111
3 |25 Smith Street | Suite 200 | Somewhere|NH | 11111

If I get rid of the code, and then key the entire string it will work, but
that seems a little overboard to me. If I don't however I am afraid I could
get a situation where the data after the code is duplicated.

Thoughts?

Thanks
Daryl

 

[Daryl G.]

Some corrections inline. Sorry for the poorly writen message-

 

[John Vinson]

Is there a way to ensure the uniqueness of a record without stringing all
the fields together as a key?

You can create a unique Index on a field without that index
constituting a Primary Key. Just use the Indexes tool on the toolbar;
name an index (e.g. UniqueAddress); and select up to ten fields.

 

[Daryl G.]

Thanks!

You can create a unique Index on a field without that index
constituting a Primary Key. Just use the Indexes tool on the toolbar;
name an index (e.g. UniqueAddress); and select up to ten fields.

 

[Ken Snell]

10 fields, I think....

 

[Douglas J. Steele]

Oh sure, make me look bad for a field! <g>

You're right, Ken. That's what I get for relying on my memory.

 

[Ken Snell]

I did say, "I think"..... < g >