Could not delete from specified tables

[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;

 

[Pieter Wijnen]

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

Pieter

 

[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]

 

[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!