Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Newbie
  
Join Date: Aug 2007
Posts: 9
#1: Aug 11 '07
Hello all!

I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same.

My problem may be a simple find duplicates / do something with only one of the duplicates issue, but I got to the point where an update query with a nested select subquery would work wonderfully, if only it would work!

I have several fields in a master Access 2000 table, some of which are id, fname, lname, and birth_date. I can't send two birthday cards to the same kid, so I dedup in my list based on fname, lname and birth_date. The id field is autonumber, so every id is unique. With the duplicates found, I'd like to null these three fields in only ONE of the entries. So I first wrote a select query that returned the result set I wanted to modify:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [fname], MAX([id])
  2. FROM master AS Tmp
  3. GROUP BY [fname], [lname], [birth_date]
  4. HAVING Count(*)>1  And [fname] = master.[fname] And [lname] = master.[lname] And [birth_date] = master.[birth_date];
  5.  
This displays just one of each duplicate entry, so I then place it as a nested SELECT in an UPDATE query like so:

Expand|Select|Wrap|Line Numbers
  1. UPDATE master SET [fname] = Null, [lname] = Null, [birth_date] = Null
  2. WHERE (SELECT DISTINCT [fname], MAX([id])
  3. FROM master AS Tmp
  4. GROUP BY [fname], [lname], [birth_date]
  5. HAVING Count(*)>1  And [fname] = master.[fname] And [lname] = master.[lname] And [birth_date] = master.[birth_date]);
  6.  
This of course, results in access telling me I can't run this query:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

Of course, modifying my SELECT query to filter by only one field would result in me seeing all entries that have duplicates. I've tried an AND to join two different SELECT subqueries but that of course doesn't work. I'm at wits end, but we do this manually now so it wouldn't be a big loss if what I want to do is out of the question.

I am new to the world of database management, and any kind of code writing, so I apologize if this is exceedingly basic. I appreciate any and all help!

-Eddie

ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#2: Aug 11 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by P3Eddie

Hello all!

I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same.

My problem may be a simple find duplicates / do something with only one of the duplicates issue, but I got to the point where an update query with a nested select subquery would work wonderfully, if only it would work!

I have several fields in a master Access 2000 table, some of which are id, fname, lname, and birth_date. I can't send two birthday cards to the same kid, so I dedup in my list based on fname, lname and birth_date. The id field is autonumber, so every id is unique. With the duplicates found, I'd like to null these three fields in only ONE of the entries. So I first wrote a select query that returned the result set I wanted to modify:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [fname], MAX([id])
  2. FROM master AS Tmp
  3. GROUP BY [fname], [lname], [birth_date]
  4. HAVING Count(*)>1  And [fname] = master.[fname] And [lname] = master.[lname] And [birth_date] = master.[birth_date];
  5.  
This displays just one of each duplicate entry, so I then place it as a nested SELECT in an UPDATE query like so:

Expand|Select|Wrap|Line Numbers
  1. UPDATE master SET [fname] = Null, [lname] = Null, [birth_date] = Null
  2. WHERE (SELECT DISTINCT [fname], MAX([id])
  3. FROM master AS Tmp
  4. GROUP BY [fname], [lname], [birth_date]
  5. HAVING Count(*)>1  And [fname] = master.[fname] And [lname] = master.[lname] And [birth_date] = master.[birth_date]);
  6.  
This of course, results in access telling me I can't run this query:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

Of course, modifying my SELECT query to filter by only one field would result in me seeing all entries that have duplicates. I've tried an AND to join two different SELECT subqueries but that of course doesn't work. I'm at wits end, but we do this manually now so it wouldn't be a big loss if what I want to do is out of the question.

I am new to the world of database management, and any kind of code writing, so I apologize if this is exceedingly basic. I appreciate any and all help!

-Eddie

Here is one solution to your problem, it has been thoroughly tested and is fully functional:
  1. Create qryUniqueDupIDs which does nothing more than display the MAXIMUM Unique ID Numbers for Duplicate Records. I used tblBirthdayCards for testing purposes, make your own substitutions.
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT Max(ID) AS Max_ID
    2. FROM tblBirthdayCards
    3. GROUP BY fname, lname, birth_date
    4. HAVING fname=[fname] AND lname=[lname] AND birth_date=[birth_date] AND Count(*)>1;
    5.  
  2. Create a Recordset based on this Query, and set the relevant, duplicate Fields to Null.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
    2.  
    3. Set MyDB = CurrentDb()
    4. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
    5.  
    6. MyRS.MoveFirst
    7.  
    8. DoCmd.SetWarnings False
    9. Do While Not MyRS.EOF
    10.   MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
    11.   MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
    12.     DoCmd.RunSQL MySQL
    13.     MyRS.MoveNext
    14. Loop
    15. DoCmd.SetWarnings True
    16.  
    17. MyRS.Close
    18.  
  3. There is probably an easier, SQL approach, but since I am not that proficient in SQL, and I don't even like it, I provide you with this alternative.
  4. Let me know how you make out.
Newbie
  
Join Date: Aug 2007
Posts: 9
#3: Aug 13 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Wow, the thought of doing this in VB never crossed my mind, as a priority of mine of late has been to keep my form code as lean as possible. This worked wonderfully! Now I'll just find a way to automate the function to loop as many times as necessary (in the event of one entry having multiple duplicates, as this works for an entry having one duplicate. ) I expect an if statement in VB ought to do the trick, pointing at an EXISTS select query.

Thank you very much for your help, exactly what I needed!
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#4: Aug 13 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by P3Eddie

Wow, the thought of doing this in VB never crossed my mind, as a priority of mine of late has been to keep my form code as lean as possible. This worked wonderfully! Now I'll just find a way to automate the function to loop as many times as necessary (in the event of one entry having multiple duplicates, as this works for an entry having one duplicate. ) I expect an if statement in VB ought to do the trick, pointing at an EXISTS select query.

Thank you very much for your help, exactly what I needed!

Glad I could help you. Just off the top of my head, you can check for the cases where there may be > 1 Duplicate via the DCount Function. If there are multiple Duplicates, then adjust the SQL Clause as indicated in Line #11. BTW, this would not involved any Looping Structure.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
  2.  
  3. Set MyDB = CurrentDb()
  4. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
  5.  
  6. MyRS.MoveFirst
  7.  
  8. DoCmd.SetWarnings False
  9. Do While Not MyRS.EOF
  10.   MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  11.   MySQL = MySQL & "[birth_date] = Null Where [ID]<>" & MyRS![Max_ID]
  12.     DoCmd.RunSQL MySQL
  13.     MyRS.MoveNext
  14. Loop
  15. DoCmd.SetWarnings True
  16.  
  17. MyRS.Close
Newbie
  
Join Date: Aug 2007
Posts: 9
#5: Aug 14 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by ADezii

Glad I could help you. Just off the top of my head, you can check for the cases where there may be > 1 Duplicate via the DCount Function. If there are multiple Duplicates, then adjust the SQL Clause as indicated in Line #11. BTW, this would not involved any Looping Structure.

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
  2.  
  3. Set MyDB = CurrentDb()
  4. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
  5.  
  6. MyRS.MoveFirst
  7.  
  8. DoCmd.SetWarnings False
  9. Do While Not MyRS.EOF
  10.   MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  11.   MySQL = MySQL & "[birth_date] = Null Where [ID]<>" & MyRS![Max_ID]
  12.     DoCmd.RunSQL MySQL
  13.     MyRS.MoveNext
  14. Loop
  15. DoCmd.SetWarnings True
  16.  
  17. MyRS.Close

Just thought you'd like to see what I came up with to resolve this thread. Any suggestions are appreciated (if you have any)!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Function4_Click()
  2.  
  3. On Error GoTo Err_Function4_Click
  4.  
  5. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String, DTotal As Long
  6. Set MyDB = currentdb()
  7. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
  8. MyRS.MoveFirst
  9.  
  10. GoTo NullDups
  11.  
  12. NullDups:
  13.     DoCmd.SetWarnings False
  14.     Do While Not MyRS.EOF
  15.         MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  16.         MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
  17.         DoCmd.RunSQL MySQL
  18.         MyRS.MoveNext
  19.         If MyRS.RecordCount = 0 Then Exit Do
  20.         Loop
  21.  
  22. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
  23. DTotal = DCount("fname", "qryfinddups")
  24.  
  25. If DTotal = 0 Then MsgBox "Success" Else GoTo NullDups
  26.  
  27. DoCmd.SetWarnings True
  28.  
  29. MyRS.Close
  30.  
  31. Exit_Function4_Click:
  32.     Exit Sub
  33.  
  34. Err_Function4_Click:
  35.     MsgBox Err.Description
  36.     Resume Exit_Function4_Click
  37.  
  38. End Sub
  39.  
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#6: Aug 14 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by P3Eddie

Just thought you'd like to see what I came up with to resolve this thread. Any suggestions are appreciated (if you have any)!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Function4_Click()
  2.  
  3. On Error GoTo Err_Function4_Click
  4.  
  5. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String, DTotal As Long
  6. Set MyDB = currentdb()
  7. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
  8. MyRS.MoveFirst
  9.  
  10. GoTo NullDups
  11.  
  12. NullDups:
  13.     DoCmd.SetWarnings False
  14.     Do While Not MyRS.EOF
  15.         MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  16.         MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
  17.         DoCmd.RunSQL MySQL
  18.         MyRS.MoveNext
  19.         If MyRS.RecordCount = 0 Then Exit Do
  20.         Loop
  21.  
  22. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
  23. DTotal = DCount("fname", "qryfinddups")
  24.  
  25. If DTotal = 0 Then MsgBox "Success" Else GoTo NullDups
  26.  
  27. DoCmd.SetWarnings True
  28.  
  29. MyRS.Close
  30.  
  31. Exit_Function4_Click:
  32.     Exit Sub
  33.  
  34. Err_Function4_Click:
  35.     MsgBox Err.Description
  36.     Resume Exit_Function4_Click
  37.  
  38. End Sub
  39.  

Just a couple of Items:
  1. MyRS was already opened in Line #7, why are you re-opening the Recordset again in Line #22?
  2. Why Line #19? The Do..Loop structure will not allow the Recordset to reach EOF, and if there are no Records, the Loop will never be entered.
  3. Except in Error Traps, GoTo Statements are frowned upon, and should only be used in rare cases. They should be replaced with some kind of looping structure. I am referring to Lines 10 and 25.
  4. Nice job of following up!
Newbie
  
Join Date: Aug 2007
Posts: 9
#7: Aug 14 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by ADezii

Just a couple of Items:

  1. MyRS was already opened in Line #7, why are you re-opening the Recordset again in Line #22?
  2. Why Line #19? The Do..Loop structure will not allow the Recordset to reach EOF, and if there are no Records, the Loop will never be entered.
  3. Except in Error Traps, GoTo Statements are frowned upon, and should only be used in rare cases. They should be replaced with some kind of looping structure. I am referring to Lines 10 and 25.
  4. Nice job of following up!

1. I wasn't sure how to "refresh" the recordset to find the new set of duplicates. So say one entry had 2 duplicates, and two entries had one duplicate each, running the procedure exactly as you had it would result in the entry with two duplicates having only one duplicate. If I looped the procedure, it would be looking at the same recordset query, for which it had already updated the relevant fields. This accomplishes the equivalent of re-running the query to pull up the set of duplicates missed, if any.

2. Without this line, the procedure entered an endless loop. Not being familiar with looping structures, this was my failsafe to make sure it would come back out if it ended up updating a recordset with no records.

3. GoTo was more my way of segmenting everything. I originally had a Do...Loop within a Do...Loop that looked something like
Expand|Select|Wrap|Line Numbers
  1. Do
  2.     Do While Not MyRS.EOF
  3.         MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  4.         MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
  5.         DoCmd.RunSQL MySQL
  6.         MyRS.MoveNext
  7.         Loop
  8.     Loop Until MyRS.RecordCount=0
  9.  
This produced unpredictable results and more often than not resulted in an endless loop for my trouble. I know GoTo and hence why, but I'll take another stab at a loop structure. Part of my problem was determining that I was trying to loop back to update a recordset that had already been updating without telling access to pull up a new set of records to update every time it finished updating the last set until it returned no duplicates. To be honest, I didn't know it was frowned upon.

Thank you so much for your time and helpful comments! I will try it again and post it here if I can get one to work a little more neatly.
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#8: Aug 14 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by P3Eddie

1. I wasn't sure how to "refresh" the recordset to find the new set of duplicates. So say one entry had 2 duplicates, and two entries had one duplicate each, running the procedure exactly as you had it would result in the entry with two duplicates having only one duplicate. If I looped the procedure, it would be looking at the same recordset query, for which it had already updated the relevant fields. This accomplishes the equivalent of re-running the query to pull up the set of duplicates missed, if any.

2. Without this line, the procedure entered an endless loop. Not being familiar with looping structures, this was my failsafe to make sure it would come back out if it ended up updating a recordset with no records.

3. GoTo was more my way of segmenting everything. I originally had a Do...Loop within a Do...Loop that looked something like

Expand|Select|Wrap|Line Numbers
  1. Do
  2.     Do While Not MyRS.EOF
  3.         MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  4.         MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
  5.         DoCmd.RunSQL MySQL
  6.         MyRS.MoveNext
  7.         Loop
  8.     Loop Until MyRS.RecordCount=0
  9.  
This produced unpredictable results and more often than not resulted in an endless loop for my trouble. I know GoTo and hence why, but I'll take another stab at a loop structure. Part of my problem was determining that I was trying to loop back to update a recordset that had already been updating without telling access to pull up a new set of records to update every time it finished updating the last set until it returned no duplicates. To be honest, I didn't know it was frowned upon.

Thank you so much for your time and helpful comments! I will try it again and post it here if I can get one to work a little more neatly.

First of all, I commend your efforts. Please understand that I am not criticizing your work, but only making suggestions that will hopefully improve your overall understanding of the coding process, as well as making your code more efficient. All of us wish that a lot of the Members here would have your initiative. Keep up the good work!
Quote:
I wasn't sure how to "refresh" the recordset to find the new set of duplicates.
  1. You can try the Requery Method of the Recordset Object. This Method will re-execute the Query on which the Object is based. Try replacing Line #22 with:
    Expand|Select|Wrap|Line Numbers
    1. MyRS.Requery
    Quote:
    this was my failsafe to make sure it would come back out if it ended up updating a recordset with no records
  2. If you wish to explicitly test a Recordset to make sure that it contains Records, it should be done immediately after its creation, before entering any code segments. The simplest means to do this is:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String, DTotal As Long
    2. Set MyDB = currentdb()
    3. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
    4. If MyRS.RecordCount > 0 Then
    5.   'you'll only get here if the Recordset contains Rows
    6. Else
    7.   'get outta Dodge!
    8. End If
Newbie
  
Join Date: Aug 2007
Posts: 9
#9: Aug 15 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by ADezii

First of all, I commend your efforts. Please understand that I am not criticizing your work, but only making suggestions that will hopefully improve your overall understanding of the coding process, as well as making your code more efficient. All of us wish that a lot of the Members here would have your initiative. Keep up the good work!

  1. You can try the Requery Method of the Recordset Object. This Method will re-execute the Query on which the Object is based. Try replacing Line #22 with:
    Expand|Select|Wrap|Line Numbers
    1. MyRS.Requery
  2. If you wish to explicitly test a Recordset to make sure that it contains Records, it should be done immediately after its creation, before entering any code segments. The simplest means to do this is:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String, DTotal As Long
    2. Set MyDB = currentdb()
    3. Set MyRS = MyDB.OpenRecordset("qryUniqueDupIDs", dbOpenDynaset)
    4. If MyRS.RecordCount > 0 Then
    5.   'you'll only get here if the Recordset contains Rows
    6. Else
    7.   'get outta Dodge!
    8. End If

No, please, I have no pride in this regard and I can only hope to make life a little easier for you guys that do know so I can learn and help others! I can't say enough how much I appreciate what you and the rest of the community here do for us "green" members!

So, I had already gotten a pretty lean block of code, and last post helped put the finishing touches. Now my entire sub consists (basically) of:

Expand|Select|Wrap|Line Numbers
  1. Do While Not MyRS.RecordCount = 0
  2.      'This ought to exit the Do...Loop once #7 results in an empty RS (No Duplicates)
  3.     MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  4.     MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
  5.     DoCmd.RunSQL MySQL
  6.     MyRS.MoveNext
  7.     If MyRS.EOF Then MyRS.Requery
  8.      'Once the last duplicate in the RS has been updated, it ought to pull up an updated RS and
  9. Loop
  10.      'Back to the criteria in #1
  11.  
I've tested this and it seems to work without a hitch. If you could give this one last check I'd appreciate it tremendously!

-Eddie
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#10: Aug 15 '07

re: UPDATE qry Specifying Multiple Fields in Nested SELECT subqry

Quote:

Originally Posted by P3Eddie

No, please, I have no pride in this regard and I can only hope to make life a little easier for you guys that do know so I can learn and help others! I can't say enough how much I appreciate what you and the rest of the community here do for us "green" members!

So, I had already gotten a pretty lean block of code, and last post helped put the finishing touches. Now my entire sub consists (basically) of:

Expand|Select|Wrap|Line Numbers
  1. Do While Not MyRS.RecordCount = 0
  2.      'This ought to exit the Do...Loop once #7 results in an empty RS (No Duplicates)
  3.     MySQL = "Update tblBirthdayCards Set [fname] = Null, [lname] = Null, "
  4.     MySQL = MySQL & "[birth_date] = Null Where [ID]=" & MyRS![Max_ID]
  5.     DoCmd.RunSQL MySQL
  6.     MyRS.MoveNext
  7.     If MyRS.EOF Then MyRS.Requery
  8.      'Once the last duplicate in the RS has been updated, it ought to pull up an updated RS and
  9. Loop
  10.      'Back to the criteria in #1
  11.  
I've tested this and it seems to work without a hitch. If you could give this one last check I'd appreciate it tremendously!

-Eddie

It's a slightly different approach, but if it works without a hitch, and you are happy with it, I'd say that ypu got a winner! Congratulations! One aspect of the beauty of programming is that there are usually many different ways to accomplish the same thing. See you around TheScripts.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,439 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved