DELETE QRY issue

[William]

I am trying to construct my first delete qry with no luck. I have two
tables. Table 1 has 15 records, Table 2 has 10 records shared by both
tables. i want to delete the 10 records in Table 1 that are in common with
Table 2, so that Table 1 ends up with 5 records, none of which is in common
with Table 2.

Here's what I tried:

New select query
Add both tables
Join the tables on their primary key using option #1 in the Join Properties
window.
Specify delete query
Drag the asterisk from Table 1 to the lower section of the qry window, where
the word "from" appears in the row 3.
When I VIEW the query, I see the 10 records I want to delete, but when I try
to RUN the query, I get an error message, the Help window for which is
concerned about "read only" status. I've looked at the properties of the
database and the queries, and none appear to be read only.

Ideas?

Thanks,

William

 

[Michel Walsh]

Either change the query property "Unique records" to Yes

Either edit the query in SQL view, and add DISTINCTROW right after the word
DELETE:

DELETE DISTINCTROW tableName.* FROM ...



ALWAYS preferable to make a backup before running a query which can make
massive modifications, like a delete query.



Vanderghast, Access MVP

 

[John Spencer]

You can do the following.

DELETE
FROM Table1
WHERE Table1.PrimaryKeyField in
(Select PrimaryKeyField FROM Table2)

In the query grid
-- Add your table
-- Add the primary key
-- In the criteria enter
In (SELECT [Table2].[RelatedPrimaryKey] FROM [Table2])
-- Select Query: Delete from the menu

One way to do a delete query is to construct a select query that returns
one field - the values in the primary key field of the records you want
to delete. Then you build a delete query with just one table - the
table you want to delete records from and use the first query in the
criteria.

DELETE
FROM Table1
WHERE PrimaryKeyField in
(SELECT PrimaryKeyField FROM TheSelectQuery)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[William]

--
William

Either change the query property "Unique records" to Yes

Either edit the query in SQL view, and add DISTINCTROW right after the word
DELETE:

DELETE DISTINCTROW tableName.* FROM ...



ALWAYS preferable to make a backup before running a query which can make
massive modifications, like a delete query.



Vanderghast, Access MVP

 

[William]

Thanks, changing the query "Unique Records" property to yes worked like a
charm.