Table cannot be modified

[sales@scantechintl.com]

Hello,

My problem is that somehow the PK of my [Orders] table got turned off allowing records with duplicate IDs. To get rid of the duplicates, I did the following:
1. create a new, test database.
2. copy the [Orders] table to it.
3. create a new field, [Delete] in the [Orders] table
4. write a query to find duplicate orders and make a new table[$Dup_Orders] with just the [IDOrder] field in it.
5. write a query , linking the [$Dup-Orders] table to the [Orders] table via [IDOrder]. Here’s the SQL of that query:

SELECT DISTINCTROW [$Dup_Orders].[IdOrder Field], Orders.DeleteDup
FROM [$Dup_Orders] INNER JOIN Orders ON [$Dup_Orders].[IdOrder Field] = Orders.IdOrder;

With that query, I would like to go through the table, flag the records to delete, and then write a query deleting those records.

The problem is that Access does not allow the [Orders] table to be modified.

The database is completely independent with only an imported [Orders] table and a temp table created from a query.

What am I doing wrong?

Regards,
John

 

[lark_tilly]

How about this:

UPDATE Orders INNER JOIN Dups ON Orders.IDOrder = Dups.IDOrder SET Orders.DeleteFlag = "Y";

This finds the rcds in the Orders table which have a match in the Dups table and sets the DeleteFlag to Y.

 

[sales@scantechintl.com]

Hi,
I haven't yet tried your suggestion but I was able to work out two solutions.
#1. after creating the [Dup] table, I modified it so that the IDorder was a PK. That then allowed me to check the delete flag in the [Order] table in a form.

#2. The other way to do it, which was much faster, was to make a new table query with the [Order] table. I grouped the records on the IDorder field, effectively eliminating duplicate records. I then modified the new table, making IDorder the PK, and then renamed the table to [Orders].

Thanks again for your response.

 

[lark_tilly]

Good, glad you figured it out.