Delete Query

[reginaldmerritt]

I have three tables which are linked by their primary keys

TBCourse (CourseID,CourseName)
TBEvent (EventID,EventName,CourseID)
TBPrice (PriceID,Price,EventID)

When I want to delete a Course I also want to delete all Events associated , which is very straight forward, I just use a delete query on TBEvents where the CourseID is equal to the Course I'm deleting.

This is the SQL used for deleting all the Events in TBEvent associated to the Course by CourseID

DELETE TBEvent.CourseID
FROM TBEvent
WHERE (((TBEvent.CourseID)=[Forms]![FRMCourseDetails].[CourseID]));
 

However, at the same time I also want to delete all the Prices associated with all the Events that will be deleted. I'm not quite sure how this could be done.

Each CourseID will have many EventID's which need to be deleted from TBPrice.Is there a way using an expression in a delete query to do this?

Any suggestions welcome.
Thanks.

[NeoPa]

Use 'Cascade delete Related Records' in the relationship created to link the tables together. Then you simply delete a course record and everything related goes too.

[reginaldmerritt]

Thanks Neo Pa that works really well.

[NeoPa]

Pleased to help :-)