delete from many tables at the same time

[kikeman]

Hi I am new using Access, and this may be an elemental question, but anyway,

I have three tables:

Customers.
Orders.
Payments. (Customers' payments)

Customers has as primary key the name of the customer and the other two
tables have as a primary key an automatic ID integer.

How can I configure Access in the way that If I erase a Customer from the
table Customers the other two tables will be automatically updated erasing
also their Orders and Payments?

Thanks,
Enrique.

 

[Jerry Whittle]

Go to the Relationships window and, if not already done, create a
relationship between the Customers table and each of the other tables. Enable
Referiental Integrity and set Cascade Delete.

If Access won't let you do this, you probably have some 'orphans' with an
Order or Payment not having a valid Customer. You'll need to fix those
problems.

Then deleting a Customer will delete the matching records in the other tables.

BUT! Do you really want to lose this kind of information?

 

[Jeff Boyce]

I'm with Jerry ... do you really want to lose that data?

You've described how you are trying to solve some issue, by deleting data.
If you'll describe what the issue is, folks here may be able to offer
alternate approaches.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[kikeman]

BUT! Do you really want to lose this kind of information?
It was a theoretical scenario, but I need to do the trick for other purposes

Then deleting a Customer will delete the matching records in the other tables.

If I delete from Customers table or from relationship table?
I would like to delete from Customer table, because I would delete from a C#
application I would like to use only one delete command for Customers table,
so that, the others will be deleted automatically. I guess when you said
"Enable Referiental Integrity", I created a relationship and set the
relationship line from "Customer Name" to Customer Name to the other tables.
Is this what did you mean by enabling Referencial integrity?

I am using Access 2003, I could not find "Cascade Delete".

Thanks,
kikeman.

 

[erika gomez]

ignacio de torres se hace pasar por mi madre y me pega palizas mientras
duermo me he levantado con la muñeca y el pie rotos kiero que me pongan
vigilancia

 

[erika gomez]

esta noche

 

[kikeman]

Hi, it works! I found the "Relationship tables" icon I made the relationship
and I selected the join line and from properties it is the "Enforce
Referencial integrity" and the "Cascade Delete Related Records" ...

I'm with Jerry ... do you really want to lose that data?

It is just a theoretical scenario, actually I would like to use this schema
for other purposes where certain data is not longer required if certain entry
that belongs to the primary key column from the "main table" (Customers
Table) is erased.

Thanks for your help

 

[erika gomez]

creo ke estan con su abuela mejor porque me kedo mas trankila