CODEWITHSUNDEEP
sqlms-accessms-access-2010sql-delete
DTM
DTM

Reputation: 11

Deleting records based on a criteria within the table and a field in another table

Created the below query using the design view in Access 2010.

The query runs and brings the correct records but when trying to run it to delete the records, I receive the error message:

Could not delete from specified table.

I am trying to delete the records with status "I" or "p" AND the year is equal to the year stored in another table.

I really appreciate any help you could provide.

I have tried building the query in different ways, I searched for solutions similar to the one I have. I am new to Access and never used SQL before. I am just learning it

DELETE Tbl_W.*, Tbl_W.Status, Tbl_Year.[Current Year]
FROM Tbl_W, Tbl_Year
WHERE (((Tbl_W.Status)="p" Or (Tbl_W.Status)="i") AND ((Tbl_AGIFYear.[Current Year])=[current year]));

To be able to delete the records identified by the query.

Upvotes: 1

Views: 53

Answers (1)

Lee Mac
Lee Mac

Reputation: 16025

In MS Access, you can only delete records from one table at a time. It is also unnecessary to specify fields which should be deleted, as only entire records can be deleted.

Given the above information, your delete query could become:

delete from tbl_w
where 
tbl_w.status in ("i", "p") and 
tbl_w.year in (select tbl_year.[current year] from tbl_year)

Or, if you are only using the tbl_year to provide a record containing the current year, you could alternatively use the Year and Date functions in the following way:

delete from tbl_w
where tbl_w.status in ("i", "p") and tbl_w.year = year(date())

Upvotes: 2

Related Questions