INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Delete query to delete every last remaing record in group of records

Delete query to delete every last remaing record in group of records

(OP)
I have a table in which records are identified by 4 different numbers. The table tabulates the results of marketing department’s focus group surveys.

The 4 different identifier numbers are the (1) Participant’s unique ID; (2) Survey type number; (3) Survey Case Number and (4) Survey Instance number.

These 4 numbers are required because the participants take different surveys but the results need to be saved in one table called ‘Survey_Results’.

Many participants start their survey but don’t complete them.

When a survey is opened, the system automatically assign the 4 numbers i.e. the Participant’s unique ID is derived from the login, Type number is assigned from opening the survey, Survey Case Number and Survey Instance number are assigned the value 0 (zero).

As the participants take different surveys of the same type number, the case and instance numbers are assigned to the attempts.

When participants start their survey but don’t complete them, they create dummy entries which take up several rows in the table.

I am trying to delete these “place holder” records where there is a Participant ID, Survey type number = x and Survey Case Number = 0 and Survey Instance number = 0.

Since all attempts are given a place holder record, I don’t want to delete the place holders for completed surveys.

Is there a way to build a delete query which would delete records where only the place holder records exist but not delete place holder records of completed surveys i.e. Survey Case Numbers > 0 and Survey Instance numbers > 0?

Thank you for your help.

RE: Delete query to delete every last remaing record in group of records

"delete query which would delete records where only the place holder records exist but not delete place holder records of completed surveys i.e. Survey Case Numbers > 0 and Survey Instance numbers > 0? "

Wouldn't that be simply:

DELETE FROM Survey_Results
WHERE Survey_Case_Number = 0
AND Survey_Instance_Number = 0

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Delete query to delete every last remaing record in group of records

Andy's suggestion should work unless you want to deleted completed questions from incomplete surveys. misuser2k7, can you confirm or clarify?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Delete query to delete every last remaing record in group of records

(OP)

Andy and Duane,

Thank you for responding to my question.

To clarify the issue further ….. Here’s an exract of the ‘Survey_Results’ table with the key numbers ….

PartcpntID SurveyNumber SurveyTypeNumber SurveyCaseNumber SurveyInstNumber
--------------------------------------------------------------------------------------------------------------------------------
10007650 1 7 0 0 <-- Keep
10007650 1 7 1 1
10007650 1 7 1 2
10007650 1 7 1 3
10007650 2 7 0 0 <-- Keep
10007650 2 7 1 1
10007650 2 7 1 2
10007650 2 7 1 3
10007650 3 7 0 0 <-- Keep
10007650 3 7 1 1
10007650 3 7 1 2
10007650 3 7 1 3
10007650 4 7 0 0 <-- Keep
10007650 4 7 1 1
10007650 4 7 1 2
10007650 4 7 1 3
10007650 5 7 0 0 <-- Delete
10007650 6 7 0 0 <-- Keep
10007650 6 7 1 1
10007650 6 7 1 2
10007650 7 7 0 0 <-- Delete
10007650 8 7 0 0 <-- Keep
10007650 8 7 1 1
-------------------------------------------------------------------------------------------------------------------------------

As you can see I would like to keep/retain the rows where Survey Case Number = 0 and Survey Instance number = 0 when there are other complete or incomplete records associated with the survey number.

In the above table extract, I would like to keep/retain the placeholder records in Survey numbers 1, 2, 3, 5 6, and 8 because these surveys have other (“subordinate”) records associated with them.
I would like to delete survey numbers 5 and 7 because they are “lone” place holder records!

To give you a file folder/sub folder analogy/example. Say I have a collection of folders on my C: Drive, some of the folders have sub folders in them, others don’t. To conserve space on the drive, I want to delete only the folders that don’t have sub folders. Deleting a folder with sub folders would delete the subfolders in it which I don’t want to do.

I am sorry for not providing this premise clearly in my original posting.

Again, thank you for your help.

RE: Delete query to delete every last remaing record in group of records

You need support query to find sets (PartcpntID, SurveyNumber, SurveyTypeNumber) with Count=1. From your example it looks like they all have SurveyCaseNumber=0 and SurveyInstNumber=0 and you need to delete them. Use the support query to identify records to delete in Survey_Results.

combo

RE: Delete query to delete every last remaing record in group of records

It looks from your sample that you want to Delete records where there is only one record for SurveyNumber.

If that's the case, try:

DELETE FROM Survey_Results
WHERE SurveyNumber IN
   (SELECT SurveyNumber
    FROM Survey_Results
    GROUP BY SurveyNumber
    HAVING ( COUNT(SurveyNumber) = 1 ))
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Delete query to delete every last remaing record in group of records

(OP)

Andy,

Your solution works on the sample that I posted but not on the actual table.

I tried the solution with a simple select query and then with the delete query but it didn't work with the actual table.

Also, is there a way I can use your solution with only a certain survey type numbers? There are only about 4 type numbers of surveys that I have this master/child like set up. All other types of surveys are stand alone types with no sub types.

Thank you for your help.

RE: Delete query to delete every last remaing record in group of records

Quote (misuser2k7)

Your solution works on the sample that I posted but not on the actual table.

Unfortunately, we cannot see your "actual table", all we can work with is the sample you provided. sad

I said: "It looks from your sample that you want to Delete records where there is only one record for SurveyNumber." Is this a correct statement?
If not, provide a sample of your data representing your issue, and describe your issue.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Delete query to delete every last remaing record in group of records

misuser2k7,
Rule #1 of any support forum is:
#1 Don't post "but it didn't work" without suggesting
- What were the results
- What did you do to troubleshoot

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close