data / record duplication multiuser dbase

[nathan]

Hello,

Has anyone experienced that records get duplicated by itself. When I
look in my tables backend mdb file there are 2 records with the same
information. I'm sure the frontend created this records. How can i solve
this problem? There are 3 users that connect to the backend file on a
windows 2003 share. My basic form uses subforms, and a search field
combobox created by access. There is one table with clients, on table
with volunteers and a table with appointments wich can be bound to a
client and a volunteer. The appointments subform is always present on
the client and volunteers form.

Thanks in advance,

Nathan

[Tom van Stiphout]

On Sun, 08 Jul 2007 00:31:46 +0200, nathan <ur*****@wanadoo.nlwrote:

A good database design prevents illogical duplicates with primary keys
and unique indexes.

-Tom.

>Hello,

Has anyone experienced that records get duplicated by itself. When I
look in my tables backend mdb file there are 2 records with the same
information. I'm sure the frontend created this records. How can i solve
this problem? There are 3 users that connect to the backend file on a
windows 2003 share. My basic form uses subforms, and a search field
combobox created by access. There is one table with clients, on table
with volunteers and a table with appointments wich can be bound to a
client and a volunteer. The appointments subform is always present on
the client and volunteers form.

Thanks in advance,

Nathan

[nathan]

nathan wrote:

Hello,

Has anyone experienced that records get duplicated by itself. When I
look in my tables backend mdb file there are 2 records with the same
information. I'm sure the frontend created this records. How can i solve
this problem? There are 3 users that connect to the backend file on a
windows 2003 share. My basic form uses subforms, and a search field
combobox created by access. There is one table with clients, on table
with volunteers and a table with appointments wich can be bound to a
client and a volunteer. The appointments subform is always present on
the client and volunteers form.

Thanks in advance,

Nathan

Every table appointments, clients and volunteers have there own primary
keys, and unique index. De information gets duplicated but the
autonumber id is different.

[nathan]

Tom van Stiphout wrote:

On Sun, 08 Jul 2007 00:31:46 +0200, nathan <ur*****@wanadoo.nlwrote:

A good database design prevents illogical duplicates with primary keys
and unique indexes.

-Tom.

>Hello,

Has anyone experienced that records get duplicated by itself. When I
look in my tables backend mdb file there are 2 records with the same
information. I'm sure the frontend created this records. How can i solve
this problem? There are 3 users that connect to the backend file on a
windows 2003 share. My basic form uses subforms, and a search field
combobox created by access. There is one table with clients, on table
with volunteers and a table with appointments wich can be bound to a
client and a volunteer. The appointments subform is always present on
the client and volunteers form.

Thanks in advance,

Nathan

Every table has its own primary key and unique index with autonumbering.

[Max Vit]

Could it be possible that the *same* information was entered twice?
If the autonumber id is different it means the records were created at
different times.
Are the autonumber id's of the records in question created in a
consecutive fashion (i.e. 554 and 555)? If they are not then I'd be
inclined to think that the same information was entered twice at
different times.
I've seen similar issues like the one you describe and something that
assists greatly in fault finding is to create a field in your table
called 'Updated' were its default value is set to 'Now()' - this way
you could also determine timing of events.
Also, it helps if in the same table you add another field that
identifies who did the change (I use 'Who'). Then you could also link
this to a specific user / computer. This field could either get the
info from the environment variables, something like
'Environ("Username")' or simply a text box that forces the user to
identify his/her-self. This is not exactly fool-proof but for apps up
to 10 users works OK.

HTH - Max

[Tom van Stiphout]

On Sun, 08 Jul 2007 03:20:16 +0200, nathan <ur*****@wanadoo.nlwrote:

My point exactly.
If you don't want the combination of certain field(s) to be
duplicated, you need to use a unique index over those field(s) to
prevent it,.
An autonumber PK doesn't do that.

-Tom.

>Tom van Stiphout wrote:

>On Sun, 08 Jul 2007 00:31:46 +0200, nathan <ur*****@wanadoo.nlwrote:

A good database design prevents illogical duplicates with primary keys
and unique indexes.

-Tom.

>>Hello,

Has anyone experienced that records get duplicated by itself. When I
look in my tables backend mdb file there are 2 records with the same
information. I'm sure the frontend created this records. How can i solve
this problem? There are 3 users that connect to the backend file on a
windows 2003 share. My basic form uses subforms, and a search field
combobox created by access. There is one table with clients, on table
with volunteers and a table with appointments wich can be bound to a
client and a volunteer. The appointments subform is always present on
the client and volunteers form.

Thanks in advance,

Nathan

Every table has its own primary key and unique index with autonumbering.

[nathan]

Max Vit wrote:

Could it be possible that the *same* information was entered twice?
If the autonumber id is different it means the records were created at
different times.
Are the autonumber id's of the records in question created in a
consecutive fashion (i.e. 554 and 555)? If they are not then I'd be
inclined to think that the same information was entered twice at
different times.
I've seen similar issues like the one you describe and something that
assists greatly in fault finding is to create a field in your table
called 'Updated' were its default value is set to 'Now()' - this way
you could also determine timing of events.
Also, it helps if in the same table you add another field that
identifies who did the change (I use 'Who'). Then you could also link
this to a specific user / computer. This field could either get the
info from the environment variables, something like
'Environ("Username")' or simply a text box that forces the user to
identify his/her-self. This is not exactly fool-proof but for apps up
to 10 users works OK.

HTH - Max

Hi Max,

Thanks for your information, i'm not sure but there is a possibility
someone added the information twice, i'm going to add the now field and
log the user who adds a record. I made a query on autoexec to search for
duplicates so i can catch them on the moment they appear.

Greetings,

Nathan

[rkc]

nathan wrote:

Max Vit wrote:

Thanks for your information, i'm not sure but there is a possibility
someone added the information twice, i'm going to add the now field and
log the user who adds a record. I made a query on autoexec to search for
duplicates so i can catch them on the moment they appear.

Did you read the other replies?

Why don't you just create a unique index on the fields that actually
make the record unique? That way exact duplicates can't be entered.

An autonumber is not really a primary key no matter how many times you
see it called one. A system generated number slapped into a field is
good for nothing but use as a pointer to the row when you want to join
it to row(s) in another table upon retrieval.

If you are never going to use it as a foreign key then having one is
completely pointless.

[nathan]

Tom van Stiphout wrote:

On Sun, 08 Jul 2007 03:20:16 +0200, nathan <ur*****@wanadoo.nlwrote:

My point exactly.
If you don't want the combination of certain field(s) to be
duplicated, you need to use a unique index over those field(s) to
prevent it,.
An autonumber PK doesn't do that.

-Tom.

>Tom van Stiphout wrote:

>>On Sun, 08 Jul 2007 00:31:46 +0200, nathan <ur*****@wanadoo.nlwrote:

A good database design prevents illogical duplicates with primary keys
and unique indexes.

-Tom.
Hello,

Has anyone experienced that records get duplicated by itself. When I
look in my tables backend mdb file there are 2 records with the same
information. I'm sure the frontend created this records. How can i solve
this problem? There are 3 users that connect to the backend file on a
windows 2003 share. My basic form uses subforms, and a search field
combobox created by access. There is one table with clients, on table
with volunteers and a table with appointments wich can be bound to a
client and a volunteer. The appointments subform is always present on
the client and volunteers form.

Thanks in advance,

Nathan

Every table has its own primary key and unique index with autonumbering.

Hi Tom,

I meant that the primary key is autonumbering with index and no
duplicates is on in the properties of the field. Is this what you meant
with unique index. The property Index (no duplicates).

[Tom van Stiphout]

On Wed, 11 Jul 2007 19:20:42 +0200, nathan <ur*****@wanadoo.nlwrote:

No, I meant that if currently you only have a single index (named the
PrimaryKey) on the Autonumber field, you do not have an index that
ensures some other combination of field(s) should also be unique.

Say you have a table tblShippers with ShipperID, ShipperName, and
other fields. You have the PK on the ShipperID autonumber.
But you also want ShipperName to be unique, because that's the
reality: there is only one UPS, one Fedex, etc. To enforce that, you
need to create another index, on the ShipperName field.

-Tom.

>Tom van Stiphout wrote:

>On Sun, 08 Jul 2007 03:20:16 +0200, nathan <ur*****@wanadoo.nlwrote:

My point exactly.
If you don't want the combination of certain field(s) to be
duplicated, you need to use a unique index over those field(s) to
prevent it,.
An autonumber PK doesn't do that.

-Tom.

<clip>

>>

Hi Tom,

I meant that the primary key is autonumbering with index and no
duplicates is on in the properties of the field. Is this what you meant
with unique index. The property Index (no duplicates).

[nathan]

Tom van Stiphout wrote:

On Wed, 11 Jul 2007 19:20:42 +0200, nathan <ur*****@wanadoo.nlwrote:

No, I meant that if currently you only have a single index (named the
PrimaryKey) on the Autonumber field, you do not have an index that
ensures some other combination of field(s) should also be unique.

Say you have a table tblShippers with ShipperID, ShipperName, and
other fields. You have the PK on the ShipperID autonumber.
But you also want ShipperName to be unique, because that's the
reality: there is only one UPS, one Fedex, etc. To enforce that, you
need to create another index, on the ShipperName field.

-Tom.

>Tom van Stiphout wrote:

>>On Sun, 08 Jul 2007 03:20:16 +0200, nathan <ur*****@wanadoo.nlwrote:

My point exactly.
If you don't want the combination of certain field(s) to be
duplicated, you need to use a unique index over those field(s) to
prevent it,.
An autonumber PK doesn't do that.

-Tom.

<clip>

>Hi Tom,

I meant that the primary key is autonumbering with index and no
duplicates is on in the properties of the field. Is this what you meant
with unique index. The property Index (no duplicates).

Hi Tom,

I have a field for the first, mid and last name. I have to combine those
fields to one name and make that a unique index. For so far I know, when
a user enters a new client and its not a unique index i just drops the
record and all other information that was in the record, the user should
be noticed of this. Still It's easily to get two the same clients if one
user adds f.fere and the other one f.a.fere.

Regards,

Nathan

[Tom van Stiphout]

On Thu, 12 Jul 2007 08:41:23 +0200, nathan <ur*****@wanadoo.nlwrote:

That's a situation indexes cannot help with, but other algorithms can.
I use Ratcliff-Obersthelp's SIMIL function for pattern matching. It
would tell me that those two names are very similar, and I would then
ask the user if she really wants to add a new name, or rather use an
existing one. Implementing this correctly requires more than basic
programming skills.

-Tom.

>Tom van Stiphout wrote:

>On Wed, 11 Jul 2007 19:20:42 +0200, nathan <ur*****@wanadoo.nlwrote:

No, I meant that if currently you only have a single index (named the
PrimaryKey) on the Autonumber field, you do not have an index that
ensures some other combination of field(s) should also be unique.

Say you have a table tblShippers with ShipperID, ShipperName, and
other fields. You have the PK on the ShipperID autonumber.
But you also want ShipperName to be unique, because that's the
reality: there is only one UPS, one Fedex, etc. To enforce that, you
need to create another index, on the ShipperName field.

-Tom.

>>Tom van Stiphout wrote:
On Sun, 08 Jul 2007 03:20:16 +0200, nathan <ur*****@wanadoo.nlwrote:

My point exactly.
If you don't want the combination of certain field(s) to be
duplicated, you need to use a unique index over those field(s) to
prevent it,.
An autonumber PK doesn't do that.

-Tom.

<clip>

>>Hi Tom,

I meant that the primary key is autonumbering with index and no
duplicates is on in the properties of the field. Is this what you meant
with unique index. The property Index (no duplicates).

Hi Tom,

I have a field for the first, mid and last name. I have to combine those
fields to one name and make that a unique index. For so far I know, when
a user enters a new client and its not a unique index i just drops the
record and all other information that was in the record, the user should
be noticed of this. Still It's easily to get two the same clients if one
user adds f.fere and the other one f.a.fere.

Regards,

Nathan

[Max Vit]

>That's a situation indexes cannot help with

100% Agree!

This was exactly the reason why I originally suggested to enable some
sort of audit trail by adding 2 fields to capture (a) who and (b) when
the record was created - even though it does not avoid similar records
being created at least it will provide a clear trail to undesrtand
where the problem is.