My Delete query uses only one table to delete from based on the query
result from another query using a different table.

My first query, get distinct values from an Item list, looks like this
and works great:
SELECT DISTINCT [JobLineItems].[ModelNum]
FROM JobLineItems;

Using that I identify the records in the products list that I want to
delete. This works also.
SELECT ProductsList.MFGName, ProductsList.ModelNum,
ProductsList.Description, ProductsList.ItemCost,
ProductsList.ItemRetail, ProductsList.DEPrice
FROM ProductsList, [A ModelNumQuery]
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

But, then I turn this query into a delete query. I get the error
message Cant not delete from specified tables.
DELETE ProductsList.*, ProductsList.ModelNum
FROM ProductsList
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

If I put a model number in the query it works. Ex, WHERE
(((ProductsList.ModelNum)=[ ="AMP-100"

The Products list has ModelNum as its primary key and is not related to
any other tables. IT IS USED FOR LOOKUP.
I have tried it with DISTINCTROW and without. No good.

Any help would be appreciated.

My only experience with that error message is that I get the message
when there are NO records to delete.

Ron

Ron2006 wrote:
> My only experience with that error message is that I get the message
> when there are NO records to delete.
>
> Ron
Thanks for the r€eply Ron. I checked and the records I want to
delete are there. i think I am going crazy.

Maureen

Actually, the problem here is that the query involves two tables, in
such a way that the resulting recordset is not updatable. look up
"updatable recordset" in Access help for more on the subject.

Solution to the problem at hand provided in the queries NG where the
question is also posted.

Nikos

I thank you also...

Now I know another way I can get the error.

(At least this is better than one error message I got when working on
an IBM360 ISAM DB. The message literally was: "None of the Above". We
used that system from 1977 thru 1984 and I got it twice. And right now
I can't remember what the final cause was.)

Ron

You can't delete from a query with distinct clause in the SELECT Or FROM
part

However this should work

DELETE FROM PRODUCTLIST A
WHERE EXISTS (SELECT 'X' FROM JobLineItems B WHERE B.ModelNum = A.ModelNum)

HTH

Pieter

PS Please retain the whole thread when answering threads, so we don't have
to go all the way to the top to see the original question

"Maureen227" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> My Delete query uses only one table to delete from based on the query
> result from another query using a different table.
>
> My first query, get distinct values from an Item list, looks like this
> and works great:
> SELECT DISTINCT [JobLineItems].[ModelNum]
> FROM JobLineItems;
>
> Using that I identify the records in the products list that I want to
> delete. This works also.
> SELECT ProductsList.MFGName, ProductsList.ModelNum,
> ProductsList.Description, ProductsList.ItemCost,
> ProductsList.ItemRetail, ProductsList.DEPrice
> FROM ProductsList, [A ModelNumQuery]
> WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));
>
> But, then I turn this query into a delete query. I get the error
> message Cant not delete from specified tables.
> DELETE ProductsList.*, ProductsList.ModelNum
> FROM ProductsList
> WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));
>
> If I put a model number in the query it works. Ex, WHERE
> (((ProductsList.ModelNum)=[ ="AMP-100"
>
> The Products list has ModelNum as its primary key and is not related to
> any other tables. IT IS USED FOR LOOKUP.
> I have tried it with DISTINCTROW and without. No good.
>
> Any help would be appreciated.
>

You can't delete from a query with distinct clause in the SELECT Or FROM
part

However this should work

DELETE FROM PRODUCTLIST A
WHERE EXISTS (SELECT 'X' FROM JobLineItems B WHERE B.ModelNum = A.ModelNum)

HTH

Pieter

PS Please retain the whole thread when answering threads, so we don't have
to go all the way to the top to see the original question

"Maureen227" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> My Delete query uses only one table to delete from based on the query
> result from another query using a different table.
>
> My first query, get distinct values from an Item list, looks like this
> and works great:
> SELECT DISTINCT [JobLineItems].[ModelNum]
> FROM JobLineItems;
>
> Using that I identify the records in the products list that I want to
> delete. This works also.
> SELECT ProductsList.MFGName, ProductsList.ModelNum,
> ProductsList.Description, ProductsList.ItemCost,
> ProductsList.ItemRetail, ProductsList.DEPrice
> FROM ProductsList, [A ModelNumQuery]
> WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));
>
> But, then I turn this query into a delete query. I get the error
> message Cant not delete from specified tables.
> DELETE ProductsList.*, ProductsList.ModelNum
> FROM ProductsList
> WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));
>
> If I put a model number in the query it works. Ex, WHERE
> (((ProductsList.ModelNum)=[ ="AMP-100"
>
> The Products list has ModelNum as its primary key and is not related to
> any other tables. IT IS USED FOR LOOKUP.
> I have tried it with DISTINCTROW and without. No good.
>
> Any help would be appreciated.
>



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len