Q:
Updating a field and counting unique field values from another fields all in the same query?
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!
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!
ASKED:
Apr 15 2009 10:59 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
With rsMessageIn
.Fields(5) = 1 'Set record uniqueness status
.Update
End With
Should be:
With rsMessageIn
.Fields(0) = 1 'Set record uniqueness status
.Update
End With
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:
SQL = "select unique,amount, count(amount) from tblmessagein group by unique,amount having count(*) = 1"
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:
SQL = "SELECT unique,amount FROM tblmessagein t WHERE EXISTS (select amount, count(*) from tblmessagein WHERE amount = t.amount GROUP BY amount HAVING COUNT(*) = 1)”
If you don’t need to retrieve the records for other purposes, you could update them in one go, this way:
SQL = "UPDATE tblmessagein t SET unique=1 WHERE EXISTS (select amount, count(*) from tblmessagein WHERE amount = t.amount GROUP BY amount HAVING COUNT(*) = 1)”
