Duplicate Records 
Author Message
 Duplicate Records

I also posted this in the "Queries" Newsgroup...but, then
I thought this may not be able to be done with
queries...VBA may be required...so, I'm posting it here,
too.

I need to do two things:

1) Delete Records (leaving 1 record) who have a common
value in a certain field. So, for example, if I have 10
records, and 3 of them have "A" in field one, then I want
the resulting table to have 8 records with only one record
containing "A" in field one.
2) Disallow adding duplicate records with common values in
a certain field...using an append query. So, for example,
I have 10 records, and I run an append query that contains
5 records, three of which have "A" in field one. I want
the resulting table to have 13 records, with only one of
those records having the newly appended "A" in field one.

Thanks in advance!!



Fri, 28 Oct 2005 22:45:24 GMT  
 Duplicate Records


Quote:

> 1) Delete Records (leaving 1 record) who have a common
> value in a certain field. So, for example, if I have 10
> records, and 3 of them have "A" in field one, then I want
> the resulting table to have 8 records with only one record
> containing "A" in field one.

How do you know which records to delete and which one to keep?

Quote:
> 2) Disallow adding duplicate records with common values in
> a certain field...using an append query. So, for example,
> I have 10 records, and I run an append query that contains
> 5 records, three of which have "A" in field one. I want
> the resulting table to have 13 records, with only one of
> those records having the newly appended "A" in field one.

Protect the field by setting a Unique Index on it. This will prevent any
records with the same value being appended. But you will have to work out
some way to control which one gets in and which are rejected: as far as
Access is concerned it's first come, first served.

B Wishes

Tim F



Sat, 29 Oct 2005 02:20:13 GMT  
 Duplicate Records
I have a Data Entry Form which updates one table.  I
created an Unique Index of about 5 of the fields to
eliminate accidental entry of a duplicate record.  It
appears to be working, but how can I capture this and
display a message to the user, that this has happened?

Thanks, Chris



Sat, 05 Nov 2005 02:27:10 GMT  
 Duplicate Records

Quote:
> I have a Data Entry Form which updates one table.  I
> created an Unique Index of about 5 of the fields to
> eliminate accidental entry of a duplicate record.  It
> appears to be working, but how can I capture this and
> display a message to the user, that this has happened?

What you could do is to use the Before Update event to Find in the
underlying recordset on all the Fields that would define a duplicate, and
issue a MsgBox and cancel the update if a record already existed. That's a
user-friendly approach.

  Larry Linson
  Microsoft Access MVP



Sat, 05 Nov 2005 09:39:26 GMT  
 Duplicate Records
Can you give me some syntax on how to do this Find?  Can I
use the Index name?  This is not a field name but, an
additional index. And, again, this is just one table.
      Thanks

Quote:
>-----Original Message-----


>> I have a Data Entry Form which updates one table.  I
>> created an Unique Index of about 5 of the fields to
>> eliminate accidental entry of a duplicate record.  It
>> appears to be working, but how can I capture this and
>> display a message to the user, that this has happened?

>What you could do is to use the Before Update event to
Find in the
>underlying recordset on all the Fields that would define
a duplicate, and
>issue a MsgBox and cancel the update if a record already
existed. That's a
>user-friendly approach.

>  Larry Linson
>  Microsoft Access MVP

>.



Sat, 05 Nov 2005 23:41:42 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. I am getting duplicate records, I mean everything is duplicate even Access record number

2. Duplicate record error using OnNotInList event

3. dUPLICATE RECORDS

4. duplicate records

5. copy/duplicate record problem

6. do not print duplicate record details in grouping

7. avoid duplicated record when input in a form by using VB

8. How can I make a duplicate record

9. Duplicated record check

10. Duplicate records??

11. deleting duplicate records

12. Eliminating duplicate records with abbreviated addresses

 

 
Powered by phpBB® Forum Software