Delete Records in a Join.

[BNPMike]

I guess this is simple.

I have a table FQPK and an identical table SQLB. I want to delete the records in FQPK that are also in SQLB. The applicant_house_no is a determinant. There are no keys, indexes or relationships declared.

The QBE Grid seems to resist me indicating I want to delete an item. So I amend the SQL as below. Access asks me which table I want to delete from, although I have specified the table in "FQPK.applicant_nationality".

I guess I'll have to do a sub-query but what is upsetting Access?

DELETE FQPK.applicant_nationality
FROM SOLB INNER JOIN FQPK ON SOLB.applicant_house_no = FQPK.applicant_house_no;

 

[PHV]

Either:
DELETE FQPK.*
FROM FQPK INNER JOIN SOLB ON FQPK.applicant_house_no = SOLB.applicant_house_no

Or:
DELETE FROM FQPK
WHERE applicant_house_no IN (SELECT applicant_house_no FROM SOLB)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BNPMike]

The sub query worked but Access didn't like:
DELETE FQPK.*
FROM FQPK INNER JOIN SOLB ON FQPK.applicant_house_no = SOLB.applicant_house_no

It gave the message:

Could not delete from specified tables. (Error 3086)