Can't Run Delete Query to Get Rid of Dupes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have about 2000 Duplicate records in a Table with 100,000 records. I read
the Microsoft online article about how to "Find, elimiate ... duplicate
records":
http://office.microsoft.com/en-us/access/HA010345581033.aspx?pid=CL100570041033#13

I followed the instructions EXACTLY (modifying them for my table or course).

I set up a Totals Query that shows only Distinct Records - that excludes the
Duplicates I want to delete. Following the article's procedure I set up a
Delete Query with the Table and the Totals Query. When I click View I get the
Duplicate Records I want to delete. But when I click Run I get an error
message: "Could not delete from specified tables" (Error 3086).

The Table with the Dupes and the Totals Query are linked in the Delete
Query. The database is not read-only; I can make any changes I want elsewhere
(new queries, tables, add, edit and delete individual records from a table.)

I'm not that familiar with Access' security features - but I'm the only
person that's ever developed or used this database. As far as I can tell
there is only one "user" - Admin - and I'm it. I don't recall ever setting
any security levels, establishing more than one user, etc.

Any ideas? Much appreciated. Thanks.

John D
 
Another way of removing duplicates:
1. Create a new table, the same structure as your table.
2. Define the key, so I wont be able to enter duplicates
3. Append all the data from the first table to seond one, the duplicates
wont be inserted to the name table.
4. Rename the name table to have the name of the old table.

Note: very inportant, back u your data before you try anything
 
Thanks - works like a charm. One of these days I'd like to figure out why the
other method proposed by MS didn't work.

Thanks - John D
 
John D said:
I have about 2000 Duplicate records in a Table with 100,000 records. I read
the Microsoft online article about how to "Find, elimiate ... duplicate
records":
http://office.microsoft.com/en-us/access/HA010345581033.aspx?pid=CL100570041033#13

I followed the instructions EXACTLY (modifying them for my table or
course).

I set up a Totals Query that shows only Distinct Records - that excludes
the
Duplicates I want to delete. Following the article's procedure I set up a
Delete Query with the Table and the Totals Query. When I click View I get
the
Duplicate Records I want to delete. But when I click Run I get an error
message: "Could not delete from specified tables" (Error 3086).

The Table with the Dupes and the Totals Query are linked in the Delete
Query. The database is not read-only; I can make any changes I want
elsewhere
(new queries, tables, add, edit and delete individual records from a
table.)

I'm not that familiar with Access' security features - but I'm the only
person that's ever developed or used this database. As far as I can tell
there is only one "user" - Admin - and I'm it. I don't recall ever setting
any security levels, establishing more than one user, etc.

Any ideas? Much appreciated. Thanks.

John D
Click to expand...

I always lose patience following those "examples"
(so I didn't painfully read through link),
but best guess is you did not set UniqueRecords to "Yes."

When your delete query joins 2 or more tables,
you have to use DISTINCTROW (set UniqueRecords to "Yes")

from
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

**quote**
Delete Queries
*************************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
*************************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually
when you create a new delete query in Access 2000. {or above}

To do so, follow these steps:

Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.

Click an empty area in the upper half of the query window
so that the property sheet displays "Query Properties" in the title bar.

Set the UniqueRecords property to Yes.

Save the query, close it, and then run the query.

For additional information about using a delete query to remove duplicate
records,
click the article number below to view the article in the Microsoft
Knowledge Base:

209183 ACC2000: How to Delete Duplicate Records from a Table
**unquote**

I'm not saying "example" is bad...they serve a great service for
people just getting started...
 
John D said:
I have about 2000 Duplicate records in a Table with 100,000 records. I read
the Microsoft online article about how to "Find, elimiate ... duplicate
records":
http://office.microsoft.com/en-us/access/HA010345581033.aspx?pid=CL100570041033#13

I followed the instructions EXACTLY (modifying them for my table or
course).

I set up a Totals Query that shows only Distinct Records - that excludes
the
Duplicates I want to delete. Following the article's procedure I set up a
Delete Query with the Table and the Totals Query. When I click View I get
the
Duplicate Records I want to delete. But when I click Run I get an error
message: "Could not delete from specified tables" (Error 3086).

The Table with the Dupes and the Totals Query are linked in the Delete
Query. The database is not read-only; I can make any changes I want
elsewhere
(new queries, tables, add, edit and delete individual records from a
table.)

I'm not that familiar with Access' security features - but I'm the only
person that's ever developed or used this database. As far as I can tell
there is only one "user" - Admin - and I'm it. I don't recall ever setting
any security levels, establishing more than one user, etc.
Click to expand...

I always lose patience following those "examples"
(so I didn't painfully read through link),
but best guess is you did not set UniqueRecords to "Yes."

When your delete query joins 2 or more tables,
you have to use DISTINCTROW (set UniqueRecords to "Yes")
in your delete query.

from
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

**quote**
Delete Queries
*************************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
*************************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually
when you create a new delete query in Access 2000. {or above}

To do so, follow these steps:

Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.

Click an empty area in the upper half of the query window
so that the property sheet displays "Query Properties" in the title bar.

Set the UniqueRecords property to Yes.

Save the query, close it, and then run the query.

For additional information about using a delete query to remove duplicate
records,
click the article number below to view the article in the Microsoft
Knowledge Base:

209183 ACC2000: How to Delete Duplicate Records from a Table
**unquote**

I'm not saying "example" is bad...they serve a great service for
people just getting started...
 
Back
Top