I have an important tho probably simple SQl problem

Need help, please. I have two tables, Deletes and n00ha.  Deletes consists 

of one field, recnum, containing the record numbers in n00ha that I want 

to delete.  My SQL query in Access 2000 looks like this:



DELETE n00ha.*, n00ha.RECNUM

FROM n00ha, Deletes

WHERE (((n00ha.RECNUM)=[Deletes].[recnum]));





When I click on the view button I get the 25 records I want to delete.  

But when I click on ! I get "cannot delete from specified tables."





What am I doing wrong?



Vic Heyman

Victor



A couple of things to try:



1) The DELETE statement takes a single table name as its 

first parameter. If you link the data in two tables in a 

delete query, you need to use the JOIN statement.



DELETE n00ha.*

FROM n00ha INNER JOIN Deletes ON n00ha.recnum = 

Deletes.recnum;



This also replaces the WHERE clause in your SQL.



2) The above SQL will only work if the recnum field in the 

deletes table has a unique index. I can't quite remember why!



BDS

---- Original message ----

>Date: Tue, 13 Mar 2001 13:32:24

>From: "Victor K Heyman" <victor@h...>

>Subject: [access] SQL Delete Query

>To: "Access" <access@p...>

>

>I have an important tho probably simple SQl problem

>Need help, please. I have two tables, Deletes and n00ha.  

Deletes consists 

>of one field, recnum, containing the record numbers in n00ha 

that I want 

>to delete.  My SQL query in Access 2000 looks like this:

>

>DELETE n00ha.*, n00ha.RECNUM

>FROM n00ha, Deletes

>WHERE (((n00ha.RECNUM)=[Deletes].[recnum]));

>

>

>When I click on the view button I get the 25 records I want 

to delete.  

>But when I click on ! I get "cannot delete from specified 

tables."

>

>

>What am I doing wrong?

>

>Vic Heyman

>

Thanks Brian,



You are indeed correct.  I tried adding a key autonumber field to Deletes 

but that didn't work.  But making recnum in deletes a key field did.  And 

I made recnum in the n00ha table a key field for good measure.



Thanks tremendously.  I would not have figured this out on my own.



Vic



I have an important tho probably simple SQl problem

> Need help, please. I have two tables, Deletes and n00ha.  Deletes 

consists 

> of one field, recnum, containing the record numbers in n00ha that I want 

> to delete.  My SQL query in Access 2000 looks like this:

> 

> DELETE n00ha.*, n00ha.RECNUM

> FROM n00ha, Deletes

> WHERE (((n00ha.RECNUM)=[Deletes].[recnum]));

> 

> 

> When I click on the view button I get the 25 records I want to delete.  

> But when I click on ! I get "cannot delete from specified tables."

> 

> 

> What am I doing wrong?

>