Unique records

[dongle]

I have a table with addresses in it, I need to prevent entry of another record which has the same values in the field called "street" combined with the field called "postcode".

I have tried following the instructions for setting up indexes with unique values in Access help and on these forums but I still seem to be able to input dublicate records.

Any ideas please.

 

[PHV]

Create an unique composite index on (street,postcode).

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

 

[willir]

To create the composite key...
Open the table in design mode
From the menu, "View" -> "Indexes"

Input a name for the index such as UnqAddr (unique address) under the Index name column.

Select the first field from the pick list under the FieldName column.

Under the IndexProperties section toggle Unique from "No" to "Yes".

Move to the next row, and under the FieldName column, select the next field to be included in the composited key.

If you need to add a third field, do so.

Your setup for the composite index should look something like...
[tt]
IndexName FieldName SortOrder
UnqAdd Street Ascending
PostCode Ascending
[/tt]
Where UnqAdd is configured to be unique.

Save your design.

Note that this will not prevent duplication such as
123 Main Street
123 Main Str
123 Main St.
123 Main St

And if two different people live at the same address (shared housing)???

I find that working with addresses in a database is much more involved than one would expect.

Richard

 

[dongle]

I have done this and duplicates are still allowed through.

 

[dongle]

It is now working - I can see what you mean about addresses being tricky - thanks for your help.