First I don’t think you can do what you want to do with a “Query”. You could do with a Function. You did not give detailed table and field so develop specific code could not be but here is the basic out line. I am not an expert and coding so some syntax may be cause error messages.
1) You will need to supply the function with 2 values, the % on rates to increase and the % to increase the rates.
2) The function would be something like this.
Function UpDate( lngPercent As Long, lngPercentChange as Long)
Dim intMyValue, intRecordNunber, intArrRecordCount as Integer
Dim intGroupRecordCnt As Interger ‘the number of records in a group
Dim arrRecordName() As Integer ‘ This is an array to store the record information in so the same record is not updated twice
Dim strGroupNamesSql, strGroupSql, strDuplicateRecord as String
Dim intGroupcnt as Integer ‘The number of group to be updated
Dim intNoRecordsToSelect as Integer
Dim rstGroup, rstGroupCnt As Recordset
‘Finds and stores the different Group Names
strGroupNamesSql “=SELECT DISTINCT GroupName FROM TableName;
Set rstGroupNames = db.OpenRecordset(strGroupNamesSql, dbOpenDynaset)
intGroupNamecnt = rstGroupNames.RecordCount ‘ the # of groups
DoCmd.GoToRecord acActiveDataObject, " rstGroupNames ", acFirst
strGroup = rstGroupNames !GroupName
Do While intGroupNamecnt> 0
strGroupSql = "SELECT recordno, current rate" & _
" FROM TableName WHERE GroupName = " & "'" & strGroup “’”
Set rstGroup = db.OpenRecordset(strGroupSql, dbOpenDynaset)
intGroupRecordCnt = rstGroup.RecordCount ‘the record count for the strGroup
intNoRecordsToSelect = Round(intGroupRecordCnt* lngPercent,0)
reDim arrRecordName(intNoRecordsToSelect)
intRecordNunber = 0
Do While intNoRecordsToSelect >0
If intRecordNumber = 0 then
' Generates random value between 1 and the record count for current group.
intMyValue = Int((intGroupRecordCnt * Rnd) + 1)
‘Store the record number so it will not be updated again
DoCmo.GoToRecord acActiveDataObject “rstGroup”, acGoTo, intRecordNumber
ArrRecordName(0) = rstGroup!RecordNumber
‘Calculate new rate and update record.
rstGroup.edit
rstGroup!current rate= rstGroup!current rate + rstGroup!current rate* lngPercentChange
rstGroup.update
intNoRecordsToSelect = intNoRecordsToSelect - 1
intRecordNunber = intRecordNunber + 1
Else
' Generates random value between 1 and the record count for current group.
intMyValue = Int((intGroupRecordCnt * Rnd) + 1)
‘Store the record number so it will not be updated again
DoCmo.GoToRecord acActiveDataObject “rstGroup”, acGoTo, intRecordNumber
intArrRecordCount = 0
Do While intArrRecordCount < intGroupRecordCnt
If rstGroup!RecordNumber <> ArrRecordName(intArrRecordCount ) Then
intArrRecordCount = intArrRecordCount +1
Else
strDuplicateRecord = “Yes”
End If
Loop
If strDuplicateRecord <> “Yes”
ArrRecordName(intRecordNumber) = rstGroup!RecordNumber
‘Calculate new rate and update record.
rstGroup.edit
rstGroup!current rate= rstGroup!current rate + rstGroup!current rate* lngPercentChange
rstGroup.update
intNoRecordsToSelect = intNoRecordsToSelect - 1
intRecordNunber = intRecordNunber + 1
End If
Loop
intGroupNamecnt = intGroupNamecnt - 1
Loop
End Function
