Updating Percentage of Different Segments

[tharris3]

I am trying to update a certain percentage (i.e. 10%) from each group; however I would like to make it automated because I will be using this query often and could have up to 100 different groups. Is there any code that I can use that will have it randomized based on the # of groups and people in each group?

Group # of People 10% Changed
A 100 10
B 50 5
C 30 3

 

[2Grandpa]

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