delete query with 2 tables

[bsacks]

I have a query that has 2 tables linked (one to many). Access won’t let me
create a delete query from this – it give me a message that I need to choose
the table to delete from. Can you help me?

I am an intermediate user - I do not know SQL.

thanks,

Brandy

 

[Tom Lake]

I have a query that has 2 tables linked (one to many). Access won’t let me
create a delete query from this – it give me a message that I need to choose
the table to delete from. Can you help me?

I am an intermediate user - I do not know SQL.

thanks,

In your Relationship between the two, you should have Cascade Deletes turned on.
Then you make the Delete query from the One side table only. The related records
in the Many side will be deleted automatically.

Tom Lake

 

[John W. Vinson]

I have a query that has 2 tables linked (one to many). Access won’t let me
create a delete query from this – it give me a message that I need to choose
the table to delete from. Can you help me?

I am an intermediate user - I do not know SQL.

thanks,

Brandy

You need to be sure you only specify one table in the DELETE clause. In the
query grid, there should be one or more fields with "From" and one or more
other fields with "Where" - select the * pseudo-field from the table from
which you want to delete records in the FROM column, and put any fields from
the other table in the WHERE column(s).

The SQL view of the query would resemble

DELETE TableA.*
FROM TableA INNER JOIN TableB
ON <whatever>
WHERE TableB.field = something

If this doesn't help, please open the Delete query; select View... SQL; and
post the SQL text here.

 

[bsacks]

In your Relationship between the two, you should have Cascade Deletes turned on.
Then you make the Delete query from the One side table only. The related records
in the Many side will be deleted automatically.

Tom Lake

Hi Tom,

Thanks for the reply. I have turned Cascade Delete on. How (exactly) do i
make the delete query from one side table only?

thanks!