Need a delQuey based on subtable

[HRakibul]

hello there,
i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null. please see my attachment.
:banghead::banghead:

 

[jdraw]

Do NOT use LOOKUPS at the table field level.
Read this and adjust your table accordingly.

 

[HRakibul]

Do NOT use LOOKUPS at the table field level.
Read this and adjust your table accordingly.

sorry boss i m not satisfied with ur reply. And i cant remove my look up if i wanna relate two tables. So please show an easy example.

 

[essaytee]

sorry boss i m not satisfied with ur reply. And i cant remove my look up if i wanna relate two tables. So please show an easy example.

I wouldn't be too dismissive of jdraw's suggestion. You say you're not satisfied with his reply; is it the case you didn't understand his reply? There is a difference.

Anyway, try this:

DELETE tblEmployee.EmployeeID
FROM tblEmployee
WHERE tblEmployee.EmployeeID
NOT IN
(Select EmployeeID FROM tblAddress);

I made an assumption regarding the Address table and field name, change accordingly. The SQL can be made less verbose but it is a starting point for you.

For further info regarding sub-queries check this out.

TIP: Before you start messing around with Delete Queries, first test with a normal SELECT query, this will reveal which records will be deleted without actually deleting them.