HELP Please, Delete Records based on another table (1 Viewer)

Lilly420

Registered User.
Local time
Today, 12:03
Joined
Oct 4, 2013
Messages
126
Hello,

I hope someone can help me, I have an Open Response table with records that I need removed based on the Closed Response table - they both share the OpenRespID.

I would like to use a delete query so I pulled in the tblOpenResponses, and the tblClosedResponses and joined on OpenRespID and would like it to remove those records that are equal from the tblOpenResponses – (if they exist in the tblClosedResponse) …. but not sure how to do that and I looked on line but did not understand what they are saying.

I really appreciate any help.

Thank you.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:03
Joined
Oct 29, 2018
Messages
21,358
Hi Lilly. You shouldn't have to split your table into two unless you're hitting the 2GB file size limit. In any case, have you tried converting your SELECT query into a DELETE query? If so, what happened?
 

cheekybuddha

AWF VIP
Local time
Today, 16:03
Joined
Jul 21, 2014
Messages
2,237
MAKE A BACKUP OF YOUR TABLE FIRST!!!

Either:
Code:
DELETE
  *
FROM tblOpenResponses
WHERE OpenRespID IN (
  SELECT
    OpenRespID
  FROM tblClosedResponse
);
Or:
Code:
DELETE
  o.*
FROM tblOpenResponses o
LEFT JOIN tblClosedResponse c
       ON o.OpenRespID  = c.OpenRespID 
WHERE c.OpenRespID IS NOT NULL
;


However, why not just have a table for responses and a field (a date field is best) for when the repsonse is closed?
SELECT where Closed Is Null for open responses.
SELECT where Closed Is Not Null for closed responses
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 28, 2001
Messages
26,999
I'm with cheekybuddha on this one.

If you have a single table with open AND closed responses and the only difference is a status flag, then you can purge your table of obsolete data by first doing what it takes to close the response. Then mark it closed. Then DELETE * FROM RESPONSES WHERE CLOSED = TRUE ; And if you don't delete right a way, you have historical data which might be of some value for reporting. Of course, if you want only closed records for a report, you have a status flag for selection/WHERE purposes.

The BIG advantage of this is much less data movement. The fewer times you have to move anything, the better off you are because every data move is a place where you could have a mid-process error and have a half-moved table. Definitely not good.
 

cheekybuddha

AWF VIP
Local time
Today, 16:03
Joined
Jul 21, 2014
Messages
2,237
The reason I suggested a date field as opposed to a boolean, for instance, is that it will also enable you to see when a response was closed!
 

Lilly420

Registered User.
Local time
Today, 12:03
Joined
Oct 4, 2013
Messages
126
Ok here goes...I have an OpenResponse table and a ClosedRespone table. I use an append query to move the data from one to the other after the 2nd Reviewer has put in his name and the 2nd Review Date-that is what triggers it to be closed. Then I would use a delete query to remove the Closed (based on the above criteria) from Open table and it worked fine. Mgt wants to add other fields such as a Tickler=Yes, then Ticker Due Date and Tickler Review Date and Tickler Reviewer Name...so if the 2nd reviewer signed off the first part, and if the Tickler is Yes, but not signed off yet, it needs to remain in the Open table...until the tickler review date and tickler reviewer name is entered then it can come move. I had a hard time getting this to work in the append query, so I had to do a sub-query for the tickler data and joining that with my query and it works...I get the right data. So I copied this query, and changed it to a Delete query but won't work...and I am not sure why. So then I thought do a Make-table with the closed responses and remove those with a delete query from the Open table...that is what I am trying to do.

I hope this makes sense.

Thank you.

Lilly
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 28, 2001
Messages
26,999
You can do ALL of that with a single table and your tickler fields and (at worst) a multi-state variable that says things like
Open,
WaitForReview1,
ApprovedReview1,
WaitForReview2,
ApprovedReview2,
Closed,
etc.etc.

And no movement of records whatsoever. You are describing a linear approvals process that looks just like a typical military approvals chain. I was a sys admin for the U.S.Navy for over 25 years and saw this process time and again. Always with a single state variable used to decide what was the next thing that had to be done.

Duplicate-structure tables is not only wasteful of CPU time but technically violates normalization concepts - because you have two records that would look alike and would even represent the same exact thing (responses) - but the key to the response wouldn't tell you where it was (since, I would assume, responses are asynchronous so movement to the "Closed" table wouldn't be based on strictly sequential movement).
 

Lilly420

Registered User.
Local time
Today, 12:03
Joined
Oct 4, 2013
Messages
126

Good morning,

First, thank you all for replying to me and your help.

After reading all your posts here, I decided to change it to one table called FEResponses, and not move/delete any data...this the first time I have set data up like this...and you are all right about that, I questioned myself on this when I first did it and glad I reached out to you for help.

So after combining the closed and open to one table, I put a button on the form to close the record (yes/no) when they have added all the data they want, the Manager can close the record and the form is filtered so they only see those that are not closed.

I am going to see if Management will approve this...hopefully so but if not do you think you would help me with what you posted-see below as I am not sure how to set this up?
-------------------------------
You can do ALL of that with a single table and your tickler fields and (at worst) a multi-state variable that says things like Open, WaitForReview1, ApprovedReview1, WaitForReview2, ApprovedReview2, Closed, etc.

Again, thank you all for the help and for making rethink the way I was doing this.

Lilly
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 28, 2001
Messages
26,999
do you think you would help me with what you posted

Sure. Here it is in words.

To accomplish this, you set up a table of states. Could be just two columns, but there are cases where you could have more. Column 1 could be autonumbered OR you could just manually assign numbers. This type of thing is typically so small and so rarely edited that doing it manually is easy enough. It can be a one-and-done setup.

That number becomes a state-code AND the prime key of that table. Then the 2nd column is the name of the state. Sometimes I have done this with a 3rd column, the text explanation of the state, in which case the 2nd column is the abbreviation of the state name and the 3rd column is just a long-winded sentence.

Code:
Code State Means
 1   OPEN Ready for (more) work
 2   REV1 Waiting for review by 1st level supervisor
 3   APP1 Approved by 1st level supervisor
 4   REV2 Forwarded to 2nd reviewer
 5   APP2 Approved by 2nd level supervisor
 6   CLSD Closed, nothing else to be done before archiving
 7   ARCV Ready for archiving

In fact, you can have any number of states. I picked seven for an example but YOU have to choose the states that are right for the business flow. Typically, if your transitions are ALWAYS linear then you just number them so that they have the right number in the table for that given step - 1st, 2nd, 3rd possible state in the order they could be attained.

Now, when you want to store a state to a record, you do so through a form on which you have bound fields. And for the state field, which would be numeric, instead of a text box you have a combo box linked to the state lookup table. The bound column will be the code, but in the display you can make it display the bound column with zero width, which hides the actual number, and you can have non-zero width for whatever other column you wanted to display. By diddling with the combo box's format properties, you can make the box wider while selecting but narrower if not in focus. So if you had an abbreviation AND an explanation, display them both while selecting but shrink it down when statically displayed. (Look at "Width" and "List Width" properties.)

It will be up to you to update the records in the right order. However, if you want to prevent folks from accidentally skipping a step, maybe you do it another way. Instead of allowing a combo box, only show the status as a text box that is locked. (That's another property.) If the states are in the right order, then you implement a command button that checks for the right fields being filled in and then increments the state code to the next state. And if you don't have the initials or whatever ID of the person who was supposed to review it, you don't do the increment.

To make this easy to set up, when you have your main table, its State field should have a formal relationship to the Code field in the states table. Use the relationship option where you have every State field and only the matching Code field. Then when you go to build the form, the form wizard understands established relationships and the combo wizard does, too. So building the combo on the form is a piece of cake if you have the tables and relationships already in place and were planning to use the combo method. And you don't even need that if you were planning to use the "command button step" method.

In any forms reports where you need the state name rather than the code, you would again use the established relationship to build a query that pulls in the main table and the states table. Therefore, you would be able to JOIN the main and state tables. In the query design grid, you have both the main table and the states table. When you wanted the name, you just pull down the state table's short name field, whatever you called it, rather than the main table's code field. This would be a simple example of using the state field as a FOREIGN KEY to the state table's PRIME KEY, and having the relationship again makes this a piece of cake to put together.

Now, just one more word of warning. I was using English here, not code. It is important to avoid confusion, so you would not actually call the main table's field STATE and also have a table named STATE. One of those should have another name. That way when you look at the arbitrarily named thing, you know immediately whether it is the name of the table or the name of a field based on them being different, and thus not having to look at the syntax to remember which was which. That might seem like trivial advice, but it is a touch that avoids confusion later, and that is never trivial.
 

Lilly420

Registered User.
Local time
Today, 12:03
Joined
Oct 4, 2013
Messages
126
My apologies for not getting back sooner as I was out of the office for a bit. Thank you for this, I am going to read through what you posted here and try to understand it. Again, thank you.
 

Users who are viewing this thread

Top Bottom