Updating a field and counting unique field values from another fields all in the same query?

40 pts.
Tags:
Query optimization
SQL
SQL queries
How can I structure the following query to allow me to count the number of unique entries in a field named "amount" and using the same query, update another field named "unique" Here is what I am doing: SQL = "select amount, count(amount) from tblmessagein group by amount having count(*) = 1" If rsMessageIn.State = adStateOpen Then rsMessageIn.Close rsMessageIn.Open SQL, cntblMessageIn, adOpenStatic, adLockOptimistic, -1 ElseIf rsMessageIn.State = adStateClosed Then rsMessageIn.Open SQL, cntblMessageIn, adOpenStatic, adLockOptimistic, -1 End If Dim TempID As String 'Set the number of records that have been found to be unique - used for searching, sending and updating later UniqueCount = rsMessageIn.RecordCount 'Use the Record unique count to cycle through the original 'amount values and then store them in unique message variables For ii = 1 To UniqueCount With rsMessageIn UniqueMsgText(ii) = .Fields(0).OriginalValue If UniqueCount > 1 Then .MoveNext If UniqueCount = ii Then .MoveFirst End With Next ii ' For i = 1 To UniqueCount SQLInbox3 = "select unique,amount from tblmessagein where amount = '" & UniqueMsgText(i) & "'" If rsMessageIn.State = adStateOpen Then rsMessageIn.Close rsMessageIn.Open SQL, cntblMessageIn, adOpenStatic, adLockOptimistic, -1 ElseIf rsMessageIn.State = adStateClosed Then rsMessageIn.Open SQL, cntblMessageIn, adOpenStatic, adLockOptimistic, -1 End If With rsMessageIn .Fields(5) = 1 'Set record uniqueness status .Update End With Next i ' This is what I would like to do, condense everything into one query instead of two as above, but I get "unique" is not part of the Aggregate function error: SQL = "select unique,amount, count(amount) from tblmessagein group by amount having count(*) = 1" If rsMessageIn.State = adStateOpen Then rsMessageIn.Close rsMessageIn.Open SQL, cntblMessageIn, adOpenStatic, adLockOptimistic, -1 ElseIf rsMessageIn.State = adStateClosed Then rsMessageIn.Open SQL, cntblMessageIn, adOpenStatic, adLockOptimistic, -1 End If Dim TempID As String 'Set the number of records that have been found to be unique - used for searching, sending and updating later UniqueCount = rsMessageIn.RecordCount 'Use the Record unique count to cycle through the original 'amount values and then store them in unique message variables For ii = 1 To UniqueCount With rsMessageIn rsMessageIn.Fields(5) = 1 'Set record uniqueness status rsMessageIn.Update If UniqueCount > 1 Then .MoveNext If UniqueCount = ii Then .MoveFirst End With Next ii Basicly all I need to do is get all records that are unique based on the amount field, then update a field named unique with a 1 for each of the records found to have unique amount values! Can somebody please help me out on this one, I'm pulling my hair out already, thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

<b>I just noticed an error in my code:</b>

<pre>With rsMessageIn
.Fields(5) = 1 ‘Set record uniqueness status
.Update
End With</pre>

<b>Should be:</b>

<pre>With rsMessageIn
.Fields(0) = 1 ‘Set record uniqueness status
.Update
End With</pre>

But this is irrelevant to my question, I am just correcting this for completeness!

————————

You are getting the error because you are grouping only by “amount”.

The correct query would be:

<pre>SQL = “select unique,amount, count(amount) from tblmessagein group by unique,amount having count(*) = 1″</pre>

But that is not what you want, because this query will check for the uniqueness of the unique-amount combination.

You would need something like this:

<pre>SQL = “SELECT unique,amount FROM tblmessagein t WHERE EXISTS (select amount, count(*) from tblmessagein WHERE amount = t.amount GROUP BY amount HAVING COUNT(*) = 1)”</pre>

If you don’t need to retrieve the records for other purposes, you could update them in one go, this way:

<pre>SQL = “UPDATE tblmessagein t SET unique=1 WHERE EXISTS (select amount, count(*) from tblmessagein WHERE amount = t.amount GROUP BY amount HAVING COUNT(*) = 1)”</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following