Delete query Error message

Question

I created a Delete query and when I run the query I get the following error message: "Specify the table containing the records you want to delete".

Below is all the information on the tables and the design of the query. What I need to know is where I went wrong and how do I specify the table for the records to delete. Please try to be a little specific as I am not that good at access most of what I do is from a book and searching the communities.

I have two tables in the query as follows:  1- MemberInfo       2-Dues

One to many relationship with the MemberInfo table on the one side, and the Dues table on the many side.

Referential integrity is enforced

Cascade Delete Related records is checked.

In the query I have the following in the design window.

Field: MemberID

Table: MemberInfo

Delete: Where

Field: LastName

Table: MemberInfo

Delete: Where

Field: FirstName

Table: MemberInfo

Delete: Where

Field: Deceased

Table: MemberInfo

Delete: Where

Criteria: False

Field: Dues Year

Table: Dues

Delete: Where

Criteria: <Year(Date())-2

Thanks for any help.

Answer 1

Any query with a group by can't be part of a delete query:

Try this after confirming the records:

DELETE Member_Info.*

FROM Member_Info

WHERE Expired=True And Deceased=False

And MemberID IN (SELECT MemberID from Dues WHERE DuesYear<Year(Date())-2);

Answer 2

Changed the query back to a select query and Added Group By: DuesYear= Max. Ran the select query and got the results I am looking for.

I changed the query back to a delete query and when I ran it and got the message "Specify the table containing the records you want to delete".

I then deleted the SQL that was in the query and pasted the statement you gave me in and when I ran the query I got the message "Could not delete from specified tables".

Answer 3

I would first change the query to a select query to make sure your logic is correct. You should see only records that you want to delete.

What happens if you try:

DELETE Member_Info.*

FROM Member_Info INNER JOIN Dues ON Member_Info.[MemberID] = Dues.[MemberID]

WHERE (((Member_Info.Expired)=True) AND ((Member_Info.Deceased)=False) AND ((Dues.DuesYear)<Year(Date())-2));

Answer 4

Here is the SQL statement:

DELETE Member_Info.MemberID, Member_Info.LastName, Member_Info.FirstName, Member_Info.Expired, Member_Info.Deceased, Dues.DuesYear

FROM Member_Info INNER JOIN Dues ON Member_Info.[MemberID] = Dues.[MemberID]

WHERE (((Member_Info.Expired)=True) AND ((Member_Info.Deceased)=False) AND ((Dues.DuesYear)<Year(Date())-2));

In this case I want the records from both the MemberInfo table and the Dues table to be deleted which from my reading is why I have the Cascade Delete Related Records box checked in the relationship.

In my case we don't keep any records past 2 years from the current year. I have an update query which updates anyone who is past due to expired and we keep that for 2 years from the current year. Once 3 full years pass the person would have to reapply with our national headquarters.

Hope this answers your response statement.

Thank you

Answer 5

Just provide the SQL view of the query and tell us which table you expect records to be deleted from. Also, many of us prefer to mark a record as deleted status using a status field in the table.