how to prevent inputting duplicate addresses in access form

[Sno]

I have designed a database table and form to enter data into the table. The
table contains street addresses where work is to be done. What I need to do,
is to ensure that street addresses are entered only once. The street address
field is a text field. Can anyone advise how I can do this, as some of the
street names are the same but numbers are different.

 

[bhicks11 via AccessMonster.com]

Preventing Duplicate Values in a Field
Step 1Understand that this method will prevent duplicate values from being
entered in a field. For example, you might want to ensure that you enter a
name only once in your address book.

Open a table in Design view.

In the upper part of the screen, click on the field that you want to prevent
duplicate entries in.

In the lower part of the screen, click the Indexed property box and select
Yes (No Duplicates).

Bonnie
http://www.dataplus-svc.com

 

[John W. Vinson]

I have designed a database table and form to enter data into the table. The
table contains street addresses where work is to be done. What I need to do,
is to ensure that street addresses are entered only once. The street address
field is a text field. Can anyone advise how I can do this, as some of the
street names are the same but numbers are different.

I would think carefully about your table design here. Is "312 Main St." the
same address as "312 Main" or "312 Main Street" or "312 W. Main St."?

Or maybe there's a "312 E. Main St." as well???

Managing address data can be tricky, and will depend on your problem space.
Are these all addresses in one metropolitan area? If so, you might want to get
in touch with the appropriate municipal tax authority and see if they have an
available database of addresses, or at least of street names. You may want to
break down the address into Address, Street, Direction, and Suffix (e.g.
"St.", "Ave.", "Blvd.", "Court"), and use a more finely divided form than just
a free form textbox. I'd really recommend having an Addresses table, with a
numeric AddressID, and store just this ID in your table (rather than the text
of the address).

You may need to use VBA code in the Address Form's BeforeUpdate event to
search for previous instances of the address.