Ensure Uniqueness without Keying?

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

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.
 
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
 
Is there a way to ensure the uniqueness of a record without stringing all
the fields together as a key?
Click to expand...

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.
 
Thanks!
John Vinson said:
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.
Click to expand...
 
Oh sure, make me look bad for a field! <g>

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