Remove Fields Query (1 Viewer)

[Mario]

Hello,

I have 2 tables (tblCustomer and tblMill) and my problem is the following:

tblCustomer contains only the name of the customers (named Customer)
tblMill contains many info and one field is the customer (named Customer)

Here is what I would like to do:
I delete one record from the tblMill --> Fine,
BUT after (and this is my problem)
I would like to check if every record from tblCustomer exists in tblMill and if not, delete that record from tblCustomer.

What would be the SQL code for that?

Thanks

Mario

 

[pdx_man]

Ideally you would have a Relationship set up between these two tables. Linked by the Customer field with Referential Integrity and at least the option of Cascade Delete option selected. This would be proper architecture for a Relational Database. (Look up the Bold words under Access Help) You would have to copy your data out of you tables, delete the data, set your relationships, copy the data back because existing data would probably violate the relationships. This is what I would recommend doing.

To clean the data before doing this, use the Query Wizard for Find Unmatched, then change this to a delete query.

 

[barbarossaii]

Relationship between tables

Hi Mario,

instead of deleting the records manually or by a delete query (action query), you should think about using the "relationships" of the tables: if not done yet you could define an relationship between the tables (between the customer 'columns') and edit it in so it 'enforces referential integrity'. By this Access deletes records in the one table if the respective record in the other table is deleted. But make sure this referencial integrity is really that what you want (i.e. Access deleting records 'on its own').

HTH,
Barbarossa II


Edit:

pdx_man was faster & more detailed....

 

[Mario]

Thanks to both of you. I'll do that.

Mario