Could not delete from specified tables


I

ISUTri

I created the following query but I get the "Could Not Delete From
Specified Tables" error. Although I have access to the table and can
delete from that table manually.

my query is as follows:

DELETE DISTINCTROW tblSatAdv.*
FROM tblSatAdv

INNER JOIN
(
SELECT Max(tblSatAdv.SnapshotInvDte) AS MaxOfSnapshotInvDte
FROM tblSatAdv
) AS MAXDTE
ON tblSatAdv.SnapshotInvDte = MAXDTE.MaxOfSnapshotInvDte;
 
Ad

Advertisements

P

Pieter Wijnen

It is not possible to delete using a aggregate sub Query as a joined "table"

Pieter
 
J

John W. Vinson

I created the following query but I get the "Could Not Delete From
Specified Tables" error. Although I have access to the table and can
delete from that table manually.

my query is as follows:

Alas, no Totals query is ever updateable. You can get around it by using
DMax() rather than a subquery:

DELETE DISTINCTROW tblSatAdv.*
FROM tblSatAdv
WHERE SnapshotInvDte = DMax("ShapshotInvDte", "tblSatAdv");

This will delete either a single record, or that set of records tied for the
maximum SnapshotInvDte. If you want to delete the maximum record for each
<some field> you'll need a third argument to the DMax to specify the grouping.


John W. Vinson [MVP]
 
Ad

Advertisements

I

ISUTri

Alas, no Totals query is ever updateable. You can get around it by using
DMax() rather than a subquery:

DELETE DISTINCTROW tblSatAdv.*
FROM tblSatAdv
WHERE SnapshotInvDte = DMax("ShapshotInvDte", "tblSatAdv");

This will delete either a single record, or that set of records tied for the
maximum SnapshotInvDte. If you want to delete the maximum record for each
<some field> you'll need a third argument to the DMax to specify the grouping.

John W. Vinson [MVP]

Thanks! That worked!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top