Delete Query

[Boon]

Hi,

Why the below query doesn't work? I have been in the situation that I need
to delete some records by using the query below. Access doesn't allow me to
run this query. I would like to know what is the reason and how should I do
to accomplish this task. The workaround that I was doing wasn't that good
and took quite an effort. I think maybe there is a better to do this.

Thanks.
B




DELETE TEST2.Width, TEST1.*
FROM TEST1 INNER JOIN TEST2 ON TEST1.Cus = TEST2.Cus
WHERE (((TEST2.Width)=3));

 

[John W. Vinson]

Hi,

Why the below query doesn't work? I have been in the situation that I need
to delete some records by using the query below. Access doesn't allow me to
run this query. I would like to know what is the reason and how should I do
to accomplish this task. The workaround that I was doing wasn't that good
and took quite an effort. I think maybe there is a better to do this.

Just don't include the other table in the DELETE clause:

DELETE TEST1.*
FROM TEST1 INNER JOIN TEST2 ON TEST1.Cus = TEST2.Cus
WHERE (((TEST2.Width)=3));

In the query grid you would just uncheck the Show checkbox under Test2.Width.

 

[vanderghast]

You may also try to add DISTINCTROW (when using a DELETE over a join)


DELETE DISTINCTROW TEST1.*
FROM TEST1 INNER JOIN TEST2 ON TEST1.Cus = TEST2.Cus
WHERE (((TEST2.Width)=3));



Vanderghast, Access MVP

 

[John Spencer MVP]

Easiest way is to use a sub query to identify the records instead of a join.
Assuming you want to delete records from TEST1
DELETE
FROM Test1
WHERE Test1.Cus in
(SELECT Cus
FROM Test2
WHERE Width = 3)

If you want to delete records from test 2 then

DELETE
FROM Test2
WHERE Test2.Width=3 AND
Test2.Cus in (SELECT Cus FROM Test1)

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

 

[Boon]

Thanks. It works!

I would add to your method that the checkbox is not in the Delete Query, you
need to switch to Select Query first, then uncheck it.

Thanks,