Delete Query does not work!

[Renvik]

Hi folks -

I am getting frustrated with a simple delete query.

I want to delete records from a table based on a criteria (field) in another
table and when I run the query it get the message "cold not delete from the
specified table" and help on this specifies error no. 3086 and mentions the
possible causes of failure such as ownership or read only table etc, none of
which are true in this case, because I have the ownership and have not
opened the database as read-only.
Here is the specific example:

One table has customer account records (with CustomerID as the primarykey)
and the other table contains the customerID as well as the date on which a
particular account was closed. My delete query has these two tables (common
field being the CustomerID) and I want to delete the customer records which
were closed earlier than a particular date (specified in the criteria WHERE
....) . but no luck. This seemed such a straight forward thing!!!!

What am I missing? Please help.

Thank you.
KKB

 

[Gerald Stanley]

Could you post the SQL to this thread. Please remember
that we cannot see your database.

Gerald Stanley MCSD

 

[Renvik]

Here is the SQL:

DELETE StudentRecord.*, DischargeLog.DateDischarged
FROM StudentRecord INNER JOIN DischargeLog ON StudentRecord.RegID =
DischargeLog.RegID
WHERE (((DischargeLog.DateDischarged)<Date()));

 

[Gerald Stanley]

DELETE removes entire rows from a table and cannot be used
to remove a single column.

Hope This Helps
Gerald Stanley MCSD

 

[John Spencer (MVP)]

Access won't normally (if ever) let you delete from two tables at once. That is
what you are trying to do with your query.

Try changing it to. Note that you only have a reference to ONE of the tables in
the Delete clause.

DELETE StudentRecord.*
FROM StudentRecord INNER JOIN DischargeLog ON StudentRecord.RegID =
DischargeLog.RegID
WHERE (((DischargeLog.DateDischarged)<Date()));

If that won't work then you can try this query with a subquery in the where clause


DELETE StudentRecord.*, DischargeLog.DateDischarged
FROM StudentRecord
WHERE StudentRecord.RegId IN
( SELECT DischargeLog.RegID
FROM DischargeLog
WHERE DischargeLog.DateDischarged<Date())