Inner Join SQL Issue/SQL Format

[RAOtto01]

I am new to writing SQL in MS Access 2013....so I am requesting assistance.



The database has table with Members and another table list member attendance at a list of events. I am working with a form where the user selects the events he/she wishes to delete. I created temporary table with the event and an addition field (Select_Record) to allow the user to check which records to delete.

The objective of the SQL statement is to delete records in the [Attendance Table] based on following criteria in the temporary table [tbl_TempTableDeleteRcds]:
1. [tbl_TempTableDeleteRcds].[Attendance Rcd ID] = [Attendance Table].[ID] i.e. record ID match
2. [tbl_TempTableDeleteRcds].[Select_Record]=True i.e. record marked as "selected" in the tempory table

Here is my SQL statement attached to my button "Delete Selected Records" on the form.

DELETE DISTINCTROW [Attendance Table].*
FROM [Attendance Table] INNER JOIN [tbl_TempTableDeleteRcds] ON [Attendence Table].[ID]=[tbl_TempTableDeleteRcds].[Attendance Rcd ID]
WHERE ((tbl_TempTableDeleteRcds.Select_Record)=True)

When I run the SQL statement I get a VB error "3135" Syntax error in JOIN operation.

Can you point my error?

I also tried a Query with both tables and same two criteria listed above but when I run the query it asks which table to delete the records from.

[ranman256]

You can't delete records from a join. ( thanks Microsoft) it woulda made things easy.
instead you must use a sub query using the IN statement.
Use the query wizard to build a 'Remove Duplicates' query on any table, as an example.
look at the design, just change from select query to delete query, and notice the IN statement in the first field.

[RAOtto01]

After fooling around with various formats of the SQL I found the following statement did the trick. Thanks for your help.

DELETE FROM [Attendence Table]
WHERE EXISTS
(SELECT *
FROM [tbl_TempTableDeleteRcds]
WHERE [Attendence Table].ID = tbl_TempTableDeleteRcds.[Attendance Rcd ID]
AND ((tbl_TempTableDeleteRcds.Select_Record)=True));