delete record(s) from a table (1 Viewer)

[shutzy]

i am having trouble with deleting a record from a particular table. the record is filtered out using criteria from other tables and i dont know how to specify what table to delete which record.

the record i want to delete is ClientCourseID from tblClientCourse

what am i missing?

DELETE tblOrdersItems.OrdersItemsID, tblClientCourse.ClientCourseID, tblClientCourse.ClientID, tblItems.Items, tblCourses.CourseID, tblClientCourse.DatePurchase
FROM ((tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) INNER JOIN tblCourses ON tblItems.ItemsID = tblCourses.ItemID) INNER JOIN tblClientCourse ON tblCourses.CourseID = tblClientCourse.CourseID
WHERE (((tblOrdersItems.OrdersItemsID)=[Forms]![frmDepartures-PriceChange]![OrdersItemsID]) AND ((tblClientCourse.ClientID)=[Forms]![frmDepartures]![ClientDetailsID]) AND ((tblClientCourse.DatePurchase)=Date()));

 

[jzwp22]

You should only have the table where the record exists in the FROM clause. Also, no other tables should be referenced in the DELETE Clause. So the basic structure should look like this.

DELETE ClientCourseID FROM tblClientCourse

Of course, now you have to identify the actual clientcourseID that needs to be deleted, so we need to add the criteria. In this case the criteria will be a subquery that will look similar to the query you posted less all of the unnecessary fields & substituting SELECT for DELETE

DELETE ClientCourseID FROM tblClientCourse
WHERE ClientCourseID IN (SELECT tblClientCourse.ClientID FROM ((tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) INNER JOIN tblCourses ON tblItems.ItemsID = tblCourses.ItemID) INNER JOIN tblClientCourse ON tblCourses.CourseID = tblClientCourse.CourseID
WHERE (((tblOrdersItems.OrdersItemsID)=[Forms]![frmDepartures-PriceChange]![OrdersItemsID]) AND ((tblClientCourse.ClientID)=[Forms]![frmDepartures]![ClientDetailsID]) AND ((tblClientCourse.DatePurchase)=Date())))

 

[MarkK]

In a delete query you don't even need the fields, so...

DELETE FROM tblYourTable

...deletes every record the table (so be careful, and keep back-ups). To delete a specific record, or a number of records that satisfy a condition, include a where clause, like...

DELETE FROM tblYourTable WHERE IDField = Forms!frmTest.IDField