what if / goal seek function

[smig]

Is there any WhatIf or GoalSeek function in Access ?

Here is what I need:
I have a table with about 500 records.
I want to take one field intNumberOfPeople, multiply it in the number I'm looking for (X) that can be any number between 0 to 1. finaly I need to Round the result (Again, it's number of people)
Now I need to sum all the Rounded multiplied numbers and get as close as I can to a constant number constY

constY = Sum(Round(intNumberOfPeople * X))

any idea ?

Thanks,
Tal

 

[vbaInet]

The short answer would be "No" smig.

If you want that functionality I would advise that you export your dataset to Excel and run it from Excel. Otherwise, elaborate a little bit on what you're trying to achieve.

 

[smig]

I managed to create a function that do exactly what I need
It even do it better then Excel as it can also find a very close result in case it can't find an acurate one.
It will take no longer then 23 itirations to find the final result.

Sub cretaeTichnunMultiply()
 
Dim lngCurrTest As Long
Dim lngLastTest As Long
Dim BottomBoundry As Double
Dim TopBoundry As Double
Dim i As Long
i = 0
BottomBoundry = 0
TopBoundry = 10
lngRerquiredValue = MyRequiredSum
 
lngLastTest = 0
Do
    ' -- Data saved into registery so I can get it later with the query when creating the final report
    SaveSetting "TichnunGius", "TichnunGius", "Multiply", BottomBoundry + (TopBoundry - BottomBoundry) / 2
    lngCurrTest = DSum("[MyValue]", "MyTable")
    If lngCurrTest = lngRerquiredValue Or (lngCurrTest = lngLastTest And Abs(lngCurrTest - lngRerquiredValue) <= 3) Or i = 50 Then
        Exit Do
    Else
        If lngCurrTest < lngRerquiredValue Then
            BottomBoundry = BottomBoundry + (TopBoundry - BottomBoundry) / 2
        Else
            TopBoundry = BottomBoundry + (TopBoundry - BottomBoundry) / 2
        End If
    End If
    i = i + 1
    lngLastTest = lngCurrTest
    Debug.Print lngCurrTest
Loop
    Debug.Print "Final: Multiply = " & GetSetting("TichnunGius", "TichnunGius", "Multiply")
    Debug.Print "Final: lngCurrTest = " & lngCurrTest
    Debug.Print "Final: i = " & i

End Sub

 

[vbaInet]

Perhaps you can upload your solution at some point for others to benefit.

 

[smig]

Perhaps you can upload your solution at some point for others to benefit.

You'r right
I edited my reply and added the code

 

[vbaInet]

Thanks for putting up your solution.