delete query - could not delete from specified table

[יריב החביב]

Hello,

i am trying to delete (in delete query) records in a table.

where in the delete query the table is join (3 inner) to a query (select).

i get the message . . . "could not delete from specified table"

is it posibble ? where i am wrong ?

Thank's

תודה רבה

 

[Ezra Mangubat]

Hello,

i am trying to delete (in delete query) records in a table.

where in the delete query the table is join (3 inner) to a query (select).

i get the message . . . "could not delete from specified table"

is it posibble ? where i am wrong ?

Thank's

תודה רבה

 

[Ken Snell]

The error means that ACCESS cannot identify the unique records that you want
to delete.

Post the SQL statement of the delete query that you're trying to run, and we
can show you how to rewrite it so that you can delete the desired records.

 

[יריב החביב]

Thank You !

Here is the code,

DELETE BasicSalleryAllValues.*
FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON
(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND
(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND
(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);

 

[יריב החביב]

Thank You Very Very Much ! ! !
--
תודה רבה

This should do it:

DELETE *
FROM BasicSalleryAllValues
WHERE EXISTS
(SELECT *
FROM QMonth_Key_choice
WHERE QMonth_Key_choice.Timing_calc
= BasicSalleryAllValues.Sub_Timing
AND QMonth_Key_choice.CalcPeriod
= BasicSalleryAllValues.Timing
AND QMonth_Key_choice.FixedDate
= BasicSalleryAllValues.FixedDate);

The subquery will only return a row where there is a match with the current
row of the outer query, so by using the EXISTS predicate to restrict the
outer query only those rows with matches will be deleted.

When undertaking set operations like this its imperative that you back up the
BasicSalleryAllValues table first of course, until you are absolutely sure
its deleting the correct rows.

Ken Sheridan
Stafford, England

Thank You !

Here is the code,

DELETE BasicSalleryAllValues.*
FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON
(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND
(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND
(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);

The error means that ACCESS cannot identify the unique records that you want
to delete.

[quoted text clipped - 17 lines]

.

--
Message posted via AccessMonster.com


.