Record cant contain the same word, how?

[dewilde]

Im new here so Hi!

My name is jitze de wilde. Im from the netherlands. Im 21. I work at www.visualmedia.nl

On my current asignment im collecting data on all the business in the local area.
I aquired alot of information and there are bount to be duplicates.

now my question. Is there a way in Access to fish out duplicate items or records (dont know the name)?

example:

I enter the following data.

Kreeft Holding B.V.
Agarica B.V.
S.I.T. Controls B.V.
Robatechniek B.V.
Mannak Techniek B.V.
Kip Caravans B.V.
Van Venrooy B.V.
Robatechniek B.V.

In the list Robatechniek B.V. exists twice. Is there a way access will prompt a message "item already exists" or something like that?

 

[neileg]

Two thoughts.

You can apply an index to the field and set it to 'No Duplicates'. However this will only stop exact duplicates. If you enter Robatechniek B.V. and Robatechniek BV then this will not be a duplicate.

Or, you could use a combo box in a form. One of the properties of a combo box is that as you type in data, it will zoom to existing entries that match what you have typed. This might help the user spot that the data already exists.

 

[dewilde]

Thanks! this will help me alot. But when i index my table, access tells me it cant go through with it because there are duplicate records.

 

[Ron_dK]

You might try creating a query, based on the company table. Ensure that the name field is one of the fields in that query. While in the query design mode, click at the query properties and set the property of "Unique values" to Yes.

Hoop dat dit helpt, succes.

 

[dewilde]

I cant find the "Unique values" in the the query properties.

I am using Access 2003

 

[Ron_dK]

While in the query design mode, click on the grey pane next to your table ( upper right area) and hit : alt + enter. This will show you the properties of the query. In here you'll find the unique value settings.

 

[dewilde]

Ah yes found it, thanks!

 

[grnzbra]

To find exact duplicates in fields, you could create an aggregate query, grouping on the name field (or whatever the field in which you are looking for duplicates) and then doing a count on the same field. Set the criteria for > 1 in the count field. This will give you a list of all entries that occur more than once.

If you have a list of valid entries for this field, you could then run an unmatched query against this field to find values that aren't "correct". Some may be totally different than those on the official list, and others will be close enough to tell that they entry errors and can be corrected.

I don't know if this is the "official" or most efficient way to do this, but it should work to some extent.