Need help with Qry to delete records from several tables

[mych]

Hi All

I have a table TblRDs that stores Requirements and Deliverables for a team. So it stored a unique id RDNo, the TeamRefNo that Requirement of Deliverable is associated with, A flag RD which will show whether it is a Reqirement or a Deliverable, the details of the Req/Del RDDetail and finaly a flag to indicate if it is still current or redundant.

This has a link into TblChecklist which in turn links into other tables.

In my application the users selects the teams required for a particular project. These are identified as Integers that are concactanated into a single field TeamsReq in TblTeamsRqd.

I have a piece of code taht takes this single field and breaks it up into an array of team numbers

For each TeamRefNo in the array I have a query that will add a row into TblChecklist for each RDNo associated with a particular TeamRefNo for that particular project.

This works great.

Now if we need to remove a team from a particular project, I nead a delete query. This is where I'm having problems.

I need to work out a query that will delete all the rows in TblChecklist with RDNo that are associated to a given TeamRefNo for a particular ProjectID.

I am fairly sure that because of the relational links once an RDNo is deleted from TBlChecklist the corresponding RDid will cause the appropriate rows to be deleted from TblUpdatesC, TblUpdateD and TblUpdateL. These deletions will inturn delete the appropriate rows from TblIssuesD, TblSHDev, TblIssuesC, TblSHClone, TblIssuesL and TblSHLive.

I hope I've explained myself clearly and hope even more that someone can help me with my query construction. I'm do not have much experience in writing queries, I tend to use the design view and the change to sql view. But this time I've got in a total mess.

Thanks in Advance

The tables affected are....

TblTeamsRqd
ProjectID (PK)
TeamsRequired

TblChecklist
RDid (PK)
ProjectID (FK)
RDNo (FK)

TblRDs
RDNo (PK)
TeamRefNo (FK)
RD
RDDetail
Redundant

TblUpdatesD *
UidD (PK)
RDid (FK)
UpdatesD

TblIssuesD *
UidD (FK)
IssuesD
CompletedD

TblSHDev *
SHidD (PK)
UidD (FK)
SoftListD
HardListD

The last three tables also exist as

TblUpdatesC TblUpdateL
TblIssuesC TblIssuesC
TblSHClone TblSHLive

Once again thanks....

 

[markgrizzle]

In the relationships window, edit the relationship between the related tables and make sure the Enforce Referential Integrity checkbox is set.

Once that's done, you can set the Cascade Delete Related Records checkbox on. This will delete related records.

This must be done for each of the linked tables you want the deletion to occur in.
Mark

 

[mych]

Thanks Mark,

I have my enforcements in place the difficulty I am having is the initial delete query.

Before running the query I will have the TeamRefNo and the Project number which will be in a global variable glbProjID

The query will need to work out from TblRDs which RDNo are associated with a particular TeamRefNo. For each of those RDNo it finds in TblChecklist WHERE the ProjectID = GlbProjID then delete the record.

This (if I've got my enforcement correct) will once an RDNo is deleted from TblChecklist the corresponding RDid will cause the appropriate rows to be deleted from TblUpdatesC, TblUpdateD and TblUpdateL. These deletions will inturn delete the appropriate rows from TblIssuesD, TblSHDev, TblIssuesC, TblSHClone, TblIssuesL and TblSHLive

I need help constructing my delete

Thanks

 

[markgrizzle]

Mych
I don't think you can pass a variable to a stored query as a parameter. You may have to store the value in a table (and include it in your delete query) if you really need to use a stored query.

Another option is to programmatically grab the query's sql, and modify it on the fly, but that may be more trouble than it's worth. If you want the stored query for optimization benefits, keep in mind you may not be getting much bang for the buck, if the parameter changes each time.

I would write a function to handle the initial delete, passing it the glbProjID as an argument.
Mark

 

[mych]

Done It!

My code for deleting is now

'Code to remove RDs for TeamRemoved from TblChecklist
SearchTeamNo = TeamRemoved
strSQL = "DELETE TblChecklist.*, TblRDs.TeamRefNo, TblChecklist.ProjectID "
strSQL = strSQL & "FROM TblRDs INNER JOIN TblChecklist ON TblRDs.RDNo = TblChecklist.RDNo "
strSQL = strSQL & "WHERE (((TblRDs.TeamRefNo)=" & SearchTeamNo & ") AND ((TblChecklist.ProjectID)='" & GlbProjID & "'));"
DoCmd.RunSQL strSQL