Reduce duplicate entries in my database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 19 Aug 2005 10:15 GMT

Hi,
This may be a little difficult to do, but I would really appreciate this
functionality in my form. I am a relative newcomer to access so please bear
with me.
I have a database which contains thousands of records in the 'data elements'
table. Each of these records must be linked to an 'interface' in a seperate
table. As you can imagine, we have a problem in entering duplicate data
elements into the data elements table. There is no way for us to see if a
similar element has been entered previously.
What would be nice is to have a subform on the elements entry form which
will show in datasheet view any records which meet criteria as the new record
is being entered. For example 'matches the first four letters of stored
element'.

This is the only idea I have had for this. How can the entry of duplicates
be minimised? I am relatively unfamiliar with code but I would love to give
some of it a try. Thanks in advance. -Ben

Reply to this Message

Jeff Boyce - 19 Aug 2005 13:08 GMT

Ben

It might help to get a bit more description of specific "data elements" and
"interfaces" -- it sounds a little like these two entities have a one-to-one
relationship, so a description of your data structure might help, too.

Another approach might be to have the current data elements listed (say, in
a combo box) and have the user start typing. The AutoExpand property of the
combo would jump to the first "data element" matching the characters entered
so far.

Regards

Jeff Boyce
<Access MVP>

> Hi,
> This may be a little difficult to do, but I would really appreciate this
[quoted text clipped - 13 lines]
> be minimised? I am relatively unfamiliar with code but I would love to give
> some of it a try. Thanks in advance. -Ben

Reply to this Message

Ben - 19 Aug 2005 13:26 GMT

The aim is to have the data element link to many interfaces. We receive about
600 interfaces which need all data elements contained in the interface to be
analysed. Currently we simply look at the interface and document all data
elements contained. This leads to a one to one relationship, but it also
means that of the 12,000 elements entered, my guess is that 40-50% are
duplicates. I would like to normalise this data and link one element to many
interfaces. Is the autoexpand function the only way to achieve this? I would
like to see more information on the data element, rather than just the name,
so I can be a bit more accurate in selecting an identical data element. Hope
this is a little clearer. Cheers.

> Ben
>
[quoted text clipped - 34 lines]
> give
> > some of it a try. Thanks in advance. -Ben

Reply to this Message

Ben - 19 Aug 2005 14:56 GMT

Just discovered exactly what I was looking for thanks to you Jeff- works
great! Just one short coming though, if I type in a data element that is not
in the combo box list (i.e not a duplicate) then how can I get Access to
create a new record in the form where the rest of the data can be entered?

> The aim is to have the data element link to many interfaces. We receive about
> 600 interfaces which need all data elements contained in the interface to be
[quoted text clipped - 45 lines]
> > give
> > > some of it a try. Thanks in advance. -Ben

Reply to this Message

Jeff Boyce - 20 Aug 2005 01:57 GMT

Ben

Check out the NotInList event -- Access HELP even has example code you could
use.

The general approach is that the combo box will fire the NotInList event if
the user tries to enter something not in the list... and this gives you the
opportunity to add a new item, if appropriate.

Regards

Jeff Boyce
<Access MVP>

> Just discovered exactly what I was looking for thanks to you Jeff- works
> great! Just one short coming though, if I type in a data element that is not
[quoted text clipped - 50 lines]
> > > give
> > > > some of it a try. Thanks in advance. -Ben

Reply to this Message