Calculate ratio between two numbers

[Truckfin]

Hi,
I'm trying to calculate a ratio between 2 numbers. ie. find the lowest common denominator between the two values and express it like this ie. 1:2
Could someone help me out with the formula?
& the best place to calculate this ie. Can I calculate this in a query?
Thanks

 

[WayneRyan]

Truck,

You can calculate it in a query, but you have to use VBA.

btw, what use is the lowest common denominator?

Wayne

 

[Truckfin]

Thanks for that Wayne.

Yeah you are right - i don't think the lcd will help me with what i want to do. Don't suppose you'd know how i'd go about calculating the ratio between 2 values to get a value1:value2. Do i just work it out as a decimal and then try and convert it back into a fraction?

 

[WayneRyan]

Truck,

What exactly do the numbers represent? If the numbers were 4 & 8;
you've referenced them as "4:8", "0.5" and "1/2".

I need a little hint here.

Wayne

 

[Truckfin]

Thanks Wayne,

the two values represent the number of offices to number of workstations.
ie. 20 offices to 250 workstations in a building. I need a way to programmaticaly reduce the two values to a tidy ratio figure. But not sure how to go about doing this. Sorry for not giving you enough background hopefully this should suffice.

 

[WayneRyan]

Truck,

OK.

Wayne

 

[Truckfin]

Thanks Wayne!

It works like a charm.

 

[amy.ostrand]

Hi Wayne - Trying this formula, but Access says undefined function for "calcGCD"- any thoughts? My data is very similar to yours...

Thanks in advance,
Amy

 

[jdraw]

It is in the Module

Here is the code for the function

Public Function CalcGCD(OneNumber As Long, OtherNumber As Long) As Long
Dim lngSmallest As Long
Dim lngGCD As Long
Dim i As Long
lngSmallest = IIf(OneNumber < OtherNumber, OneNumber, OtherNumber)
For i = lngSmallest To 1 Step -1
   If (OneNumber Mod i = 0) And (OtherNumber Mod i) = 0 Then
      CalcGCD = i
      Exit Function
   End If
   Next i
CalcGCD = 1
End Function

 

[amy.ostrand]

Sorry, I'm a newbie to Access. Where do I put that?

 

[jdraw]

Create a new module in your database. Copy and paste the code in the module.

 

[amy.ostrand]

Worked like a charm - thank you!