delete from many tables at the same time

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.




-- 
Eng. Enrique Lopez.
0
Utf
11/20/2009 4:57:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
915 Views

Similar Articles

[PageSpeed] 56

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?
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"kikeman" wrote:

> 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.
> 
> 
> 
> 
> -- 
> Eng. Enrique Lopez.
0
Utf
11/20/2009 5:15:02 PM
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" <ennrike@hotmail.com> wrote in message 
news:278422E7-30AD-4752-8050-FFD00D669C7F@microsoft.com...
> 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.
>
>
>
>
> -- 
> Eng. Enrique Lopez. 


0
Jeff
11/20/2009 6:05:30 PM
> 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.

-- 
Eng. Enrique Lopez.


"Jerry Whittle" wrote:

> 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?
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "kikeman" wrote:
> 
> > 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.
> > 
> > 
> > 
> > 
> > -- 
> > Eng. Enrique Lopez.
0
Utf
11/20/2009 6:11:02 PM
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
"kikeman" <ennrike@hotmail.com> escribió en el mensaje de noticias 
news:278422E7-30AD-4752-8050-FFD00D669C7F@microsoft.com...
> 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.
>
>
>
>
> -- 
> Eng. Enrique Lopez. 

0
erika
11/20/2009 7:26:00 PM
esta noche
"kikeman" <ennrike@hotmail.com> escribió en el mensaje de noticias 
news:278422E7-30AD-4752-8050-FFD00D669C7F@microsoft.com...
> 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.
>
>
>
>
> -- 
> Eng. Enrique Lopez. 

0
erika
11/20/2009 7:26:19 PM
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

-- 
Eng. Enrique Lopez.


"Jeff Boyce" wrote:

> 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" <ennrike@hotmail.com> wrote in message 
> news:278422E7-30AD-4752-8050-FFD00D669C7F@microsoft.com...
> > 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.
> >
> >
> >
> >
> > -- 
> > Eng. Enrique Lopez. 
> 
> 
> .
> 
0
Utf
11/20/2009 8:12:03 PM
creo ke estan con su abuela mejor porque me kedo mas trankila
"kikeman" <ennrike@hotmail.com> escribió en el mensaje de noticias 
news:278422E7-30AD-4752-8050-FFD00D669C7F@microsoft.com...
> 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.
>
>
>
>
> -- 
> Eng. Enrique Lopez. 

0
erika
11/20/2009 8:21:12 PM
Reply:

Similar Artilces:

Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that have subtotals associated with them (automatically generated via the pivot table). I need to to take the subtotals for 2009 and 2010 and calculate the % difference. Here's a screenshot of what I'm talking about: http://home.comcast.net/~wilsoch/PercentDiff.jpg Is this even possible or am I going to have to do this manually? Hi I have'nt seen the screenshot as am at work , go to Pivot Table>Formulas>Calculated Field wilsoch@comcast.net wrote: >This is what I need to do: I have rows of ...

Unread deleted messages
Hi, [Outlook 2003 SP1] When I delete messages without reading them (obvious spam, for instance) they show up in my "Unread Mail" folder. Is it possible to change this behaviour? Thanks, Jan Roelof jr_de_pijper@hotmail.com <jr_de_pijper@hotmail.com> wrote: > When I delete messages without reading them (obvious spam, for > instance) they show up in my "Unread Mail" folder. Is it possible to > change this behaviour? The "Unread Mail" folder is not actually a true folder. It's a search folder which just displays all unread messages no matt...

Calculated Field in form to table
Can you take a calculated field from a form and pull it in to a table?? ...

Deleting rows containing common data
I have an Excel 2000 spreadsheet with 30,000 rows. The rows look like this: 007-007 68-4611 68-4682 total 007-007 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 68-4682 total 007-007 68-4682 total How do I delete the rows containing "total" One way: 1. Select the range and apply an AutoFilter (Data > Filter > AutoFilter) 2. On the drop-down list, select "Total". 3. Select the range again if needed. 5. Press F5 > Special and click "Visible Cells Only." 6. Remove...

PIVOT tables and publishing thrm on a webpage
Is there a way to publish a pivot table in a html page but still kee exactly the same funcitonality that you have on a Excel spread sheet either by converting it to a java applet or something else . Any hel would be much appreciate -- Message posted from http://www.ExcelForum.com ...

Daylight Savings Time Changes
Hello, Does anyone know if the patch for Exchnage 2003 identified in KB926666 has to also be applied to every server that is running the Exchange Tools (System Manager)? Thanks in advance K On Wed, 31 Jan 2007 10:24:03 -0800, Kerry <Kerry@discussions.microsoft.com> wrote: >Hello, > >Does anyone know if the patch for Exchnage 2003 identified in KB926666 has >to also be applied to every server that is running the Exchange Tools (System >Manager)? > >Thanks in advance >K Im all for consistency. If the patch isnt needed, it shouldnt install, so I would at ...

Pivot Table question #4
Hi I have spreadsheets that take data via a pivot table from an OLAP cube that is held on a terminal server. The spreadsheets hold figures entered by me and figures obtain from the pivot table. I want to enable the automatic update on the pivot table but the pivot table is sorted by a project number obtained from the OLAP cube. My problem is if a new project is added to the OLAP cube then it is automatically selected from the drop down list. I want to be able to turn this off so that it only selects the project I have previously chosed. For example if I have selected the filter nu...

deleted email appear again
I have been using "Delete it from Server" option in Windows Mail Rules successfully for a number of years now until recently. The I noticed i began receiving mails that I have defined to be deleted from server. To further this, I used another email address defining ...

table and form not calculated in synch
Help says to open a piviot table but I am having trouble making my columns add up to total for each indivudual ID. The totals are being calculated only in th e form view which does not up date the original table and does not transfer to a report. How can I get a feild to calculate specific feilds so that I can run these reports? ...

Creating a Report using Tables (that have no relationships)
Im looking to create an invoice report that uses data from three different tables that are created dynamically by a query. The data for these will change but I would like to simply pull the data from these tables to create a report. The tables have the following information: 1. Company Info (name, address) 2. Customer Info (address, name, etc) 3. Customer Order (products, price, other info) 4. Invoice Info (ID, GST #, etc) I realize I could have created a table with relationships but the data above is dynamic as there are 4 different companies and based upon user inp...

print folder in table style
Hello all, My CU wants to print a list of files in a folder. He use Outlook 2002. Click Other Shortcuts -> My computer -> choose a folder -> Click the print icon on toolbar -> choose Table Style -> print preview The header and footer can be printed properly. However the content is blank. I mean: there is no file in the list. I can print properly at my side. However at the CU side, there are 2000 users encounter the same problem. And there are four kinds of printer. All have this problem. Any suggestion? Thanks! ...

cannot delete business unit?
Hi, I am using microsoft CRM 1.2 with demo license of Adventure works Cycle. Is there any way to delete a "Business Unit". I go to Settings -> Business Unit Settings -> Security Roles -> Business Management. The delete option for "Business Unit" and "User" is disabled there. I searched in groups and found a few discussion about deleting users which say that it is not allowed to delete mscrm users. However I did not find any reference about deletion of "Business Unit". The user guide that comes with the installation shows steps to delete a "...

Display only duplicate values and delete UNIQUE Items
All I have a very large list of data and on a monthly basis i need to display only the duplicate items in a spreadsheet. I would like to do this in VBA and then run it as a macro on the spreadsheet. Alot of the sites that i have seen only show how to removed the duplicates. Excel 2007 has a function which removed all duplicates but so far i have found nothing that only displays the duplciates.... any ideas anyone? Assuming that the field you use to determine uniqueness is column A, you can put this formula in a helper column: =3DIF(COUNTIF(A:A,A2)>1,"Duplicate","Unique&...

GP Extender Tables Question (GP9)
I have created an Extender window with 3 Fields (let’s just say price 1, price 2, price 3). I can see where it got created in table EXT40100, field User_Defined_Prompts_1 etc and I can also see where the data is stored in EXT00103. What I don’t see is where the prompts from EXT40100 are in its own rows so that you can link them to EXT00103 for reporting purposes. I know I can hard code the flags to their proper values in Crystal or set up my own custom table to do it but I would have thought that Extender will have a table like that. Am I just not seeing it? -- Pieter The prompts ...

How many screen pixels get rolled when rotating mouse wheel?
Does anyone know how to determine the vertical distance (pixels) scrolled by each rotation of mouse wheel on any scrollable client window? Rob "Rob" <xiwenm@hotmail.com> wrote in message news:1139327086.880141.44320@f14g2000cwb.googlegroups.com... > Does anyone know how to determine the vertical distance (pixels) > scrolled by each rotation of mouse wheel on any scrollable client > window? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winui/winui/windowsuserinterface/userinput/mouseinput/aboutmouseinput.asp You will have to convert from lines ...

Do Pivot Tables have an automatic data range expansion?
As I add new rows of data to my data base, my pivot table will not expand to include them after refreshing. Is there any way of setting the pivot table to include the entire database regardless of its continuously growing size? Right now, as I add a new row of data I must delete my old Pivot table and create a new one from scratch. Is this normal? You can use a dynamic range that grows/contracts with your data. Debra Dalgleish explains it all at: http://www.contextures.com/xlNames01.html#Dynamic David.c.h wrote: > > As I add new rows of data to my data base, my pivot table wil...

How can you bulk delete failed emails?
I have a client that uses a third party to "SPAM" messages out to the public from their email list out of CRM. They just sent me an Excel spreadsheet with a list of about 30 emails that failed on the marketing endeavor. Is there a way for CRM to look up these failed email addresses and remove them from the appropriate accounts so that they do not try to send to them again? write a custom application which looks for failed emails and deletes them using crm sdk. Then use windows task scheduler or create a service to call your application on a specified period, once a day or ev...

Deleting system locked fields
Does anyone know how I can get rid of the "Is revenue system calculated?" field on the Opportunities form? We always have user-provided values, and therefore have no need for this field, but I can't delete it. Anyone know how I can get around this? You can not delete those fields. But you can change the default value to "user provided". WIth some javascipt you should be able to hide it for the users. HTH, Frederic - Travi@ta www.microsoft-dynamics-crm.eu WiLLerZ schreef: > Does anyone know how I can get rid of the "Is revenue system > calculated?"...

PST deleted
During the installation of the updates post Windows 2000 Service Pack 4, Outlook XP by the user: a contact from the address book wa opened. The card has been closed without modification. Ended the modernizations it has been restarted the computer, then has been launch the 05-054 patch of Internet Explorer. After a new restart, opening Microsoft Outlook, the system says PST was deleted! Any helps ? S. Is the PST simply renamed (possibly extension changed) or sitting in the Recycle Bin? "Nospam" <nospam@nospam.org> wrote in message news:O26RWCZBGHA.2920@tk2msftngp13....

delete permanently #2
Dear Sirs, I do not want to see some mail after deleting in Delete Items folder. Can I find any macro which can delete mail permanently? Best Regards Hi QT, please Press Shift + Del -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "QT" <web_tur@yahoo.com> schrieb im Newsbeitrag news:#0mbykcaEHA.3664@TK2MSFTNGP12.phx.gbl... > Dear Sirs, > > I do not want to see some mail after deleting in Delete Items folder. Can I > find any macro which can delete mail permanently?...

Outlook locks up all the time
This is a shot in the dark. Ever since getting my new Thinkpad with Windows XP, Outlook has never seemed to work properly. I've run diagnostics on my machine and nothing comes up as a problem. I've installed a spyware program because after talking to IBM tech support, they suggested I try that. I also talked to another support tech, who suggested I reinstall Windows completely. I'm not a real technical person. Won't a re-install wipe out my files? Most of the time that I use Outlook for my email, it slows down and eventually locks up. When I restart my computer,...

Delete Messages Folder Outlook 2003
When I try to emptly the 'Delete Files' folder in outlook 2003 I get the following message: -- "This messanging interface has returned an unknown error. If the problem persists, restart outlook."---- I have restarted outlook several times and have re-booted my computer a few times and I get the same message. Can anyone help me to resolve this situation? Ted Jamison <Ted Jamison@discussions.microsoft.com> wrote: > When I try to emptly the 'Delete Files' folder in outlook 2003 I get > the following message: -- "This messanging interface has retu...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

issue with ex2k7 ccr cluster and ntbackup
hi ng have a problem with my ex2k7 ccr cluster and ntbackup. because there is not a backup software available which support ex2k7, we use ntbackup for online backup. we backup every single database. the backup is running without problems and the eventlog seems to be clean but the logs will not deleted. on a single mailbox server, which hold the public folders, i see that ntbackup works fine and the logs are deleted after a succesful full backup. has anybody heard about such a behaviour? any hint will be appreciated. regards olaf Are you by chance backing up the passive cluster node with N...

Turn on Automatic Formula Fill in Table
I turned off the 'automatically fill th column with this formula' option in Excell 2007 but I can't see how to turn it on again. Can anyone point me in teh right direction? TIA Excel 2007 Excel Options > Proofing > AutoCorrect Options > Autoformat As You Type > Fill formulas in tables to create calculated columns ...