are ratios possible?

[fishqqq]

is it possible for access to calculate a ratio
i have [field1] = 100 and [field2]=50 and i would like [field3] to =
2:1

is this possible? if so what would the expression be for field 3?
tks
S.

 

[Dirk Goldgar]

is it possible for access to calculate a ratio
i have [field1] = 100 and [field2]=50 and i would like [field3] to =
2:1

is this possible? if so what would the expression be for field 3?

I would guess you might use something like:

=IIf([Field2]=0, Null, ([Field1]/[Field2])+":1")

But I haven't tested that.

 

[fishqqq]

is it possible for access to calculate a ratio
i have [field1] = 100 and [field2]=50 and i would like [field3] to =
2:1

is this possible? if so what would the expression be for field 3?

I would guess you might use something like:

    =IIf([Field2]=0, Null, ([Field1]/[Field2])+":1")

But I haven't tested that.

Thanks for the help but this is just giving back an error message.
#error
any other suggestions?

 

[Dirk Goldgar]

Thanks for the help but this is just giving back an error message.
#error
any other suggestions?

I was trying to take a shortcut, but it didn't work. Okay, try this:

=IIf([Field2]=0 Or [Field1] Is Null Or [Field2] Is
Null,Null,([Field1]/[Field2]) & ":1")

I've tested it on a form, and it worked for me. Note that the above was
written all on one line, though it may be broken onto two lines by the
newsreader.

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through 100.
This query finds the ratio of Apples to Oranges --
SELECT Fruit.Apples, Fruit.Oranges, [Apples]/[CountNUM] & " : " &
[Oranges]/[CountNUM] AS Ratio
FROM Fruit, CountNumber
WHERE (((CountNumber.CountNUM)>1) AND
(([Apples]/[CountNUM])=Int([Apples]/[CountNUM])) AND
(([Oranges]/[CountNUM])=Int([Oranges]/[CountNUM])))
ORDER BY Fruit.Apples, Fruit.Oranges;

 

[Dirk Goldgar]

Try this,

fields - t1 t2 t3

control source for t3
=[t1]/[t2] & ":1"

That gives ":1" if either field is null, and gives #Error if t2 is 0.

 

[fishqqq]

Thanks for the help but this is just giving back an error message.
#error
any other suggestions?

I was trying to take a shortcut, but it didn't work.  Okay, try this:

    =IIf([Field2]=0 Or [Field1] Is Null Or [Field2] Is
Null,Null,([Field1]/[Field2]) & ":1")

I've tested it on a form, and it worked for me.  Note that the above was
written all on one line, though it may be broken onto two lines by the
newsreader.

This works great - Thanks. The ratio it is returning is in the
following format 8.04545454545455:1 - is there a way to have this
ready 8:1 - i tried playing around with the decimal places setting in
the control but it doesn't seem to have any effect.

Tks
Steve

 

[Dirk Goldgar]

This works great - Thanks. The ratio it is returning is in the following
format 8.04545454545455:1 - is there a way to have this ready 8:1 - i
tried playing around with the decimal places setting in the control but it
doesn't seem to have any effect.

You could use the Round() function to round the result of the division to
any desired number of decimal places. If you want to allow up to 1 decimal
place, you could use this expression:

=IIf([Field2]=0 Or [Field1] Is Null Or [Field2] Is
Null,Null,Round([Field1]/[Field2], 1) & ":1")

If you always want to round to a whole number, you could write this:

=IIf([Field2]=0 Or [Field1] Is Null Or [Field2] Is
Null,Null,Round([Field1]/[Field2], 0) & ":1")

You should be aware that the Round() function in Access uses "bankers
rounding", which tries to even out the effects of rounding error by rounding
a trailing digit 5 up if the preceding digit is odd, down if the preceding
digit is even. This isn't the same as the rounding most of us were taught
in school, where we always round 5 up. Here's an example from the immediate
window:

For x = 0.5 to 5.5 : ?x; " rounds to "; Round(x, 0) : next x
0.5 rounds to 0
1.5 rounds to 2
2.5 rounds to 2
3.5 rounds to 4
4.5 rounds to 4
5.5 rounds to 6

Note that this only matters when the digit being rounded off is 5.

If you don't want banker's rounding, you can write your own function, or get
one from (for example) here:

http://www.mvps.org/access/modules/mdl0054.htm
Modules: Implementing a custom Rounding procedure

 

[Mike Painter]

This works great - Thanks. The ratio it is returning is in the
following format 8.04545454545455:1 - is there a way to have this
ready 8:1 - i tried playing around with the decimal places setting in
the control but it doesn't seem to have any effect.

You can make it an integer or round it off.
It depends on what you are doing since X:1 is not a good solution for any
number of values.
X = 1.5 should give 3:2
X = 2.5 should give 5:2
X = 2.33 7:3

 

[fishqqq]

This works great - Thanks. The ratio it is returning is in the following
format 8.04545454545455:1 - is there a way to have this ready 8:1 - i
tried playing around with the decimal places setting in the control butit
doesn't seem to have any effect.

You could use the Round() function to round the result of the division to
any desired number of decimal places.  If you want to allow up to 1 decimal
place, you could use this expression:

    =IIf([Field2]=0 Or [Field1] Is Null Or [Field2] Is
Null,Null,Round([Field1]/[Field2], 1) & ":1")

If you always want to round to a whole number, you could write this:

    =IIf([Field2]=0 Or [Field1] Is Null Or [Field2] Is
Null,Null,Round([Field1]/[Field2], 0) & ":1")

You should be aware that the Round() function in Access uses "bankers
rounding", which tries to even out the effects of rounding error by rounding
a trailing digit 5 up if the preceding digit is odd, down if the preceding
digit is even.  This isn't the same as the rounding most of us were taught
in school, where we always round 5 up.  Here's an example from the immediate
window:

    For x = 0.5 to 5.5 : ?x; " rounds to "; Round(x, 0) : next x
     0.5  rounds to  0
     1.5  rounds to  2
     2.5  rounds to  2
     3.5  rounds to  4
     4.5  rounds to  4
     5.5  rounds to  6

Note that this only matters when the digit being rounded off is 5.

If you don't want banker's rounding, you can write your own function, or get
one from (for example) here:

   http://www.mvps.org/access/modules/mdl0054.htm
    Modules: Implementing a custom Rounding procedure

that worked perfectly - thank you very much

 

[Dirk Goldgar]

You can make it an integer or round it off.
It depends on what you are doing since X:1 is not a good solution for any
number of values.
X = 1.5 should give 3:2
X = 2.5 should give 5:2
X = 2.33 7:3

You make a good point, Mike. It would be much more interesting to return an
integer ratio, if possible, and if not, then return a ratio that is closest
to integer values. Probably more complicated than it seems, though, and I'm
not going to pursue it if Steve is happy.