delete query

[kathy]

Using Access 2000 I have a simple delete query that won't
work, but gives message 'could not delete from specified
tables'.

I have deleted and recreated tables and query. The design
view of query:

FIELD table1* suffix
TABLE table1 table2
DELETE From where

If number included in 'suffix' field of table 2 is in
table1, want it deleted from table1

This is related SQLtable1 is ANUM_COA,table2 is pulled)

DELETE ANUM_COA.*, pulled.A_NUM_SUFFIX
FROM ANUM_COA INNER JOIN pulled ON ANUM_COA.suffix =
pulled.A_NUM_SUFFIX;


Can anyone help?

 

[Michel Walsh]

Hi,

Add DISTINCTROW right after DELETE.


Remove

, pulled.A_NUM_SUFFIX


Hoping it may help,
Vanderghast, Access MVP

 

[kathy]

Thank you, that did help. Any idea why this query worked
for over a year and then stopped deleting from the table?

Then even re-creating the tables and queries did not work
without manually changing the sql?

Thanks!

 

[John Spencer (MVP)]

Try

DELETE ANUM_COA.*
FROM ANUM_COA INNER JOIN pulled
ON ANUM_COA.suffix = pulled.A_NUM_SUFFIX;

If you are using the grid, hide the field(s) in the table pulled. You can only
delete from one table at a time in Access.