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

[misuser2k7]

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.

 

[Andrzejek]

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:
[tt]
DELETE FROM Survey_Results
WHERE Survey_Case_Number = 0
AND Survey_Instance_Number = 0
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

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

 

[misuser2k7]

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.

 

[combo]

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

 

[Andrzejek]

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:

[pre]
DELETE FROM Survey_Results
WHERE SurveyNumber IN
(SELECT SurveyNumber
FROM Survey_Results
GROUP BY SurveyNumber
HAVING ( COUNT(SurveyNumber) = 1 ))
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[misuser2k7]

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.

 

[Andrzejek]

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. :-(

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.

 

[dhookom]

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