Delete from table using value in another table as critera

[Ranvier]

I have created a Delete query in Access which I want to delete records in a table 'dbo_BOOKING' where there are matching records in my second table 'dbo_COURSDET_ARCHIVE_Tmp'. I am referencing the table in the Criteria secion of the query using the 'build' function and selecting the field value for the table. When I run the query it is asking me to Enter the Parameter Value. It doesnt seem to be getting the values from the table in the criteria. Have I done this correctly and can I use other table fields in my criteria? I have put the SQL of the query below. Thanks.


DELETE dbo_BOOKING.[Course ref], dbo_BOOKING.[Course number]
FROM dbo_BOOKING
WHERE (((dbo_BOOKING.[Course ref])=[dbo_COURSDET_ARCHIVE_Tmp]![Course ref]) AND ((dbo_BOOKING.[Course number])=[dbo_COURSDET_ARCHIVE_Tmp]![Course number]));

 

[AlexCuse]

You want to join your two tables. Something like this (not tested, make sure you have a backup):

delete distinctrow dbo_BOOKING.* 
from dbo_BOOKING
inner join dbo_COURSEDET_ARCHIVE_Tmp
on dbo_BOOKING.[Course Ref] = dbo_COURSEDET_ARCHIVE_Tmp.[Course Ref]
and dbo_Booking.[Course Number] = dbo_COURSEDET_ARCHIVE_Tmp.[Course Number]

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Ranvier]

Yes is does help, many thanks.

 

[AlexCuse]

Glad it worked

Ignorance of certain subjects is a great part of wisdom