delete query that deletes data in multiple tables

  • Thread starter Thread starter Freida
  • Start date Start date
F

Freida

I am using access as a front end with sql tables. I want to know how I can
write a query that does a cascading delete since I cannot create a parent
child relationship in access b/c of the sql tables. Here is an my
scenerio,

I am doing a budget, less just say I screwed up the year and I want to start
all over again. I want to delete data from the following tables:

Budget- budget ID(which is the year), budget start date, end date,
description
Budget Accounts- budget ID, Account #(GL code), company, Budget
amount(for the GL in )each company
Budget Accounts detail- budget ID, Month, company, Account#, monthly
budget amount

I want a query that asks me for a budget year(Which is Budget ID) to delete,
and if I type in "2012" it will delete every record in all three tables
that has the budget year associated with it.
 
Without using cascade delete you will have to use three queries to
accomplish this goal.

DELETE
FROM [Budget Accounts Detail]
WHERE [Budget ID] = "2012"

DELETE
FROM [Budget Accounts]
WHERE [Budget ID] = "2012"

DELETE
FROM Budget
WHERE [Budget ID] = "2012"

This becomes a bit more complex if [Budget Id] isn't in every table.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John Spencer said:
Without using cascade delete you will have to use three queries to
accomplish this goal.

DELETE
FROM [Budget Accounts Detail]
WHERE [Budget ID] = "2012"

DELETE
FROM [Budget Accounts]
WHERE [Budget ID] = "2012"

DELETE
FROM Budget
WHERE [Budget ID] = "2012"

This becomes a bit more complex if [Budget Id] isn't in every table.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am using access as a front end with sql tables. I want to know how I can
write a query that does a cascading delete since I cannot create a parent
child relationship in access b/c of the sql tables. Here is an my
scenerio,

I am doing a budget, less just say I screwed up the year and I want to start
all over again. I want to delete data from the following tables:

Budget- budget ID(which is the year), budget start date, end date,
description
Budget Accounts- budget ID, Account #(GL code), company, Budget
amount(for the GL in )each company
Budget Accounts detail- budget ID, Month, company, Account#, monthly
budget amount

I want a query that asks me for a budget year(Which is Budget ID) to delete,
and if I type in "2012" it will delete every record in all three tables
that has the budget year associated with it.
Click to expand...
Click to expand...

ok, I guess that is going to be the way to go, but I want i box to come up
that prompts me for which budget id I want to delete? also is there a way I
can do a nested query to include all 3 of the above queries, into one large
one?
 
The simplest way to handle this is to use a form as the source.

Add a control to the form where you will enter or select from a list the
year value.

Modify the queries to use a reference to the control on the form


DELETE
FROM [Budget Accounts Detail]
WHERE [Budget ID] = Forms![Name of Form]![Name of Control]

DELETE
FROM [Budget Accounts]
WHERE [Budget ID] = = Forms![Name of Form]![Name of Control]


DELETE
FROM Budget
WHERE [Budget ID] = "= Forms![Name of Form]![Name of Control]

Add a button to the form that will run the three queries sequentially
using the Click event of the button. The code might look something like
the following.

Docmd.OpenQuery "Name of Query 1"
DoCmd.OpenQuery "Name of Query 2"
DoCmd.OpenQuery "name of Query3"

This is the SIMPLEST method I can think of. It is not necessarily the
best method.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top