Ratio calculation - Need help

[Kai]

Hi all,

I'm a newbie in access.

I need a query field to create a ratio from two numbers in the same column.

For example: I need the ratio for Asset/Liabilities which is 0.5 (=100/200) in 2008 and 0.333 (=300/900) in 2009. I don't mind if the solution is generated in a row or column.

ID:....... Account:......... $ 2008:........... $ 2009:
1 ..........Asset ...............100 .................300
2 ..........Liability ...........200 .................900
3 ..........Equity ..............350................. 500
4 ..........Revenue ..........250 .................1000

Exporting to excel is my last resort.

[TheShabz]

Try putting this into the Field: section of a new query in design view

Ratio: (SELECT [$ 2008] FROM mytable WHERE Account = "Asset")/(SELECT [$ 2008] FROM mytable WHERE Account = "Liability")

Replace "mytable" with the name of your table

[Kai]

@TheShabz - it worked, pretty close to what I've wanted. Thanks

It has given me this result:

ID:....... Account:......... $ 2008:........... $ 2009:......... Ratio
1 ..........Asset ...............100 .................300 ............... 0.5
2 ..........Liability ...........200 .................900 ................ 0.5
3 ..........Equity ..............350................. 500 ................0.5
4 ..........Revenue ..........250 .................1000...............0.5

Is there any way to work the calculation so that it adds to each row:
For example Row 5 below:

ID:....... Account:......... $ 2008:........... $ 2009:
1 ..........Asset ...............100 .................300
2 ..........Liability ...........200 .................900
3 ..........Equity ..............350................. 500
4 ..........Revenue ..........250 .................1000
5..........Asset/Liability ..0.5.................0.333

I realise that the new rows would add data/primary id's.

[TheShabz]

As a disclaimer, I wouldnt do things this way. I'd leave all calculations to reports and forms. I dont like storing calculated data in fields. That being said, try:

INSERT INTO myTable Values (Account, [$ 2008], [$ 2009])
VALUES("Asset/Liability", (SELECT [$ 2008] FROM mytable WHERE Account = "Asset")/(SELECT [$ 2008] FROM mytable WHERE Account = "Liability"), (SELECT [$ 2009] FROM mytable WHERE Account = "Asset")/(SELECT [$ 2009] FROM mytable WHERE Account = "Liability"))

I've got no idea if that'll work. Don't have Access atm to try it out first.

EDIT: The above is assuming the ID field is an autonumber, thus will be assigned. If not, put ID before Account in the Values description, and put a 5 or a "5" (depending on datatype, 5 for number "5" for text). basically make it follow the following form:

INSERT INTO myTable (Field1, Field2, Field3, etc)
VALUES (data1, data2, data3, etc)

[Kai]

Hmm, I think your right, it doesn't seem practical using a query.

I might go and do a search for Form calculations then.

Thanks for all your help TheShabz

[TheShabz]

the calculation will be done the same way. its just where the calculation takes place. You must ask yourself when you would need that data and calculate it then.