Delete query - delete records based on criteria in another table

[foxtrot123]

I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.

Here are the relevant tables:

• tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
• tblPeopleStates is a junction table between tblPeople and tblStates. It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
• tblPeopleRegions is a junction table between tblPeople and tblRegions. It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.

For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.

For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).

If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).

PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]

Any suggestions about building this query?

 

[billmeye]

Could you provide your DB?

 

[MarkK]

It seems to me you are storing the truth in two different places, for instance, a person can be linked to a region and a person can be linked to a state, but if a person is linked to a state that violates his region link then you want to delete the state link. Is that right?

If so, then why bother to link a person to a state at all? Find out what states they are linked to by querying the states in the region they are linked to.

Person->PersonRegion<-Region->State

And remove this whole concept, which is not authoritative, from your system

Person->PersonState<-State

 

[gblack]

I could never figure out how to get the delete query to do anything worthwhile, but then again I am not as smart as most of these folks... I am sure they'd be better able to help... The way I'd do it is a bit messy, but it should do the trick unless your form uses "Table B" as its source.

Create query1: Pull down table B's ID Field... Set the Join between table A and table B to get all the records from table B and only those records from table A where the join fields are equal. Then search for table A's ID field where ID IS NULL.

This should give you all the IDs you want to delete.

Create Query2: Pull down all the fields from table B. Link Query1 and table B. Set Join between Query1 and table B to get all the records from table B and only those records from query1 where the join fields are equal. Where Query1's ID Field Is Null. This will get you everything you want from table B without the extra IDs...

Turn QUERY2 from a simple select query into a make table query and call the table "B_prime" or something... Run Query2 to make B_Prime

Now Delete table B.

Change the name of B_prime to B.

To automate that process...
Throw it all in an event prcedure that looks something like this:

Dim cn As ADODB.Connection
Dim t As Table

docmd.SetWarnings False 'This'll run your make table query without confirmations
docmd.OpenQuery("QUERY2")

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data Source=C:\biblio.mdb"
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
cat.Tables("B_Prime").Name = "B"

'Iterate through the tables collection
For Each t In cat.Tables
If t.Name = "B" Then
Debug.Print t.DateModified
End If
Next

DoCmd.SetWarnings True

cn.Close
Set cn = Nothing

---------------------------------------------------------------

 

[foxtrot123]

Attached is a paired down version of my database, showing the key pieces. The form that opens has some instructional text to show what I'm trying to do. There are many ways to tackle this, and I thought that referencing a stored delete query would be the most straightforward.

Essentially, this is the issue:

When you select a region (one of 10, dynamic check boxes that get populated via OnCurrent ), the AfterUpdate event for the check box fires and runs the =AddRemoveRecord() function.

If the checkbox = True, a strSQL inserts the PersonID and RegionID into tblPeopleRegions. It then runs a stored append query, qryInsertStates, which inserts the corresponding States into tblPeopleStates. (Actually, it updates *all* of the person's states based on his region(s) in tblPeopleRegions. It doesn't really care what region was just selected.

When you UNcheck a region, the region is deleted from tblPeopleRegions, at which point I need the opposite to occur: I need to *delete* from tblPeopleStates the states that correspond to the region was that just UNchecked. (Or simply update tblPeopleStates so it corresponds to the regions in tblPeopleRegions, but we're still looking at a delete query.) I figured the easiest way is to reference a stored query, qryDeleteStates, but I couldn't figure out how to build that query.

Public Function AddRemoveRecord()
Dim x As String
Dim strSQL As String

'determine the 'number' of the check box that was updated
x = Right(Screen.ActiveControl.Name, Len(Screen.ActiveControl.Name) - 3)

If Me("chk" & x) Then
'if true, add the region
  strSQL = "INSERT INTO tblPeopleRegions ( PersonID, RegionID ) SELECT " & Me.Parent!PersonID
  strSQL = strSQL & " AS Expr1, " & Me("chk" & x).Tag & " AS Expr2"
  
    CurrentDb.Execute strSQL
    
    'now update tblPeopleStates based on the person's regions
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Set db = CurrentDb

    Set qry = db.QueryDefs("qryInsertStates")
        qry.Parameters(0) = PersonID
    qry.Execute
  
Else
'if false, delete the region
  strSQL = "Delete tblPeopleRegions.* From tblPeopleRegions WHERE PersonID = " & Me.Parent!PersonID 'Me.PersonID
  strSQL = strSQL & " AND RegionID = " & Me("chk" & x).Tag
  
    CurrentDb.Execute strSQL

[COLOR=DarkRed]'now update tblPeopleStates based on the person's regions, by deleting the states that no longer belong
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Set db = CurrentDb

    Set qry = db.QueryDefs("qryDeleteStates")
        qry.Parameters(0) = PersonID
    qry.Execute 
[/COLOR]
End If

Me.Parent!fsubPeopleStates.Requery

End Function

 

[foxtrot123]

It seems to me you are storing the truth in two different places, for instance, a person can be linked to a region and a person can be linked to a state, but if a person is linked to a state that violates his region link then you want to delete the state link. Is that right?

If so, then why bother to link a person to a state at all? ...

Not everyone will have a region assignment. Some people just have states assigned to them. Hence the need to sometimes treat them as separate constructs.

 

[MarkK]

So you can select StateIDs in a region using something like . . .

SELECT StateID FROM tState WHERE RegionID = [I]<someRegionID>[/I]

. . . and then you can delete from tPersonState where StateID "IN" that query for the given person . . .

DELETE FROM tPersonState 
WHERE PersonID = [I]<knownPersonID>[/I]
AND StateID IN ( 
   SELECT StateID 
   FROM tState 
   WHERE RegionID = [I]<someRegionID>[/I]
   )

See if that works.

I would likely create a single self-referencing table though, for states and regions, and then distinguish them with a flag, like . . .

tLocation
LocationID (PK)
ParentID (FK to the PK in this table)
Name
Abbrev
Type (flag specifies state or region or . . . )

. . . and then you only need the one tPersonLocation join table.

hth

 

[foxtrot123]

lagbolt:

Thanks to you suggestion I got it working. This is the final query that does the trick:

DELETE tblPeopleStates.PersonID, tblPeopleStates.StateID
FROM tblPeopleStates
WHERE (((tblPeopleStates.PersonID)=[Forms]![frmMain]![subform1].[Form]![subform2].[Form]![PersonID])
AND ((tblPeopleStates.StateID) 
     Not In (SELECT StateID 
     FROM tblStates
     INNER JOIN tblPeopleRegions ON tblStates.RegionID =     tblPeopleRegions.RegionID
     WHERE (((tblPeopleRegions.PersonID)=[Forms]![frmMain]![subform1].[Form]![subform2].[Form]![PersonID]));
)));

 

[MarkK]

Right on, nicely done, and thanks for posting back with your success!