Could not delete from specific table

[willy292]

Query4 is the result from left joining of Query3 and Query1
and I would like to delete some of the null data in query 4

while I execute the following code
DELETE DISTINCTROW Query4.*, [working hour] AS Expr1
FROM Query4
WHERE (((query4.[working hour])=0));

I get the warning of "Could not delete from specific table"

can anyone help, thanks.

 

[pbaldy]

As answered elsewhere:

At a guess, query4 is read only:

http://allenbrowne.com/ser-61.html

 

[willy292]

if my query has a GROUP BY clause and also union what can I do if I really want to return the relevant row only

 

[pbaldy]

I don't know your data, but either one of those makes the query read only. Perhaps you can get a key value from that query that you can use in a separate delete query.

 

[Cronk]

Will
You can't delete from an outer join query.

Make Query4 a select query, then have the delete query
Delete TableX.* FROM TableX where TableX.ID in (Select ID from Query4)

 

[Pat Hartman]

@Cronk, Yes, you can delete from an outer join. BUT, when multiple tables are included in the query, rows are only deleted from the LOWEST level table. So in a query that selects orders and order details, you can delete detail records but you CANNOT delete orders and you won't get an error message.

@Willy and PBaldy, No, you cannot delete from Union or Totals or Crosstab or any other type that loses the identity of individual records.

Willy, if your criteria for deletion is coming from a child table, you may be able to accomplish what you need by using a sub query. The outer query will check for presence in the sub query but will not select any columns from it. If you can't get the sub query to work, you can turn your current query into a make-table query, Update the table to select the PK. Then create a new delete query that joins to the make table. This is only a viable solution if this is a one-time action. If you will need to do it on a regular basis, I would create a VBA procedure that identifies the records you want to delete and then deletes them if you can't get the sub query to work.

 

[pbaldy]

@Willy and PBaldy, No, you cannot delete from Union or Totals or Crosstab or any other type that loses the identity of individual records.

Did I imply otherwise? I said "either one of those makes the query read only".

 

[Pat Hartman]

Sorry Paul. I mis-read your answer.

 

[pbaldy]

No problem Pat; just wanted to make sure I hadn't screwed up...again.

 

[Pat Hartman]

You're not one of the people who screws up. Your advice is sound and accurate.