MS Access

[MitchS]

Hi All,

I am having a problem trying to figure out how to create a ROI figure from within Microsoft Access.

To make it easy lets say i had just 2 columns in design view.

1 E2 "1" E2 top choice
2. PT ODDS >=3


Now this is a very simple example. I have columns of 20 or 30 or more, but how wouild i get a ROI from above example in MS Access?


Thanks for any help,

Mitch

[sjk]

For ROI I use a formula Avg(iif([won]=1,[odds],-1). If [won] is not one of the fields it is caluclated by iif([fin pos]=1,1,0). If [fin pos] is not one of the fields it may need to be calculated from the last point of call which can differ based on the race distance.

To get an average you use the "totals" (looks like sigma) button at the top of the query design view.

[MitchS]

Thanks sjk. Unfortunately i am not a MS Access wiz. Is this something i can plug into MS Access, and where would i do so?

Mitch

[sjk]

Mitch,

The formulas go into fields in the query design screen. You probably need to get a reference book on Access and read up on how to create queries and this should make sense.

[MitchS]

Soooo.. After a little study, if i read your post right. Of all the fields i have set up in the Query, two of them need to be Odds and Won. I would then go to the SQL View and type in

(iif([won]+1,[odds],-1)

Is this the correct procudure?


Mitch

[hurrikane]

Mitch,
you could also do this in a report field instead of the query itself.

That way you can put whatever logic you want into the query and run a report to get results

[MitchS]

Hurrikane,

This is exactly what i want to do. That way i can run any type of query, save it and run a report on the Win% and ROI. Question is... How can i do that? I would give my first born for a report template i could stick into reports. Any advice much appreicated.

Mitch

[sjk]

Mitch,

You do not need to use the SQL view at all. Use the design view and put the fields from you tables or formulas using these fields (such as I suggested above) into the field row. When you go to datafield view you will see the values of your formulas.

[MitchS]

sjk,

The expression you gave me

(iif([won]+1,[odds],-1)

Does not seem to work. I get an error message.

Undefined function 'iff' in expression.


Does anyone know how to get a WIN% and ROI in Reports?


Mitch

[sjk]

Read the expression more carefully. Unless you set up your tables with fields named [won] and [odds] copying this expression will not do you any good.

As I suggested earlier you probably need to spend some time reading up on Access.

[MitchS]

sjk.. My ignorance. I've been cuddling up with this darn Access bible (about 5" thick) all day. UGGGHHH

Mitch

[sjk]

It will take some time but I think you will find it worthwhile.

[Hammerhead]

Hey MitchS
I had some books for sale, but no takers. Get yourself something like Learning Access (whatever year you have) in 24 hours or a step by step book for Access published by microsoft.

You can get them at BorderBooks or somewhat similar Book Store. There are many out there.

[Tom]

Check out www.natsem.com
They offer workshops in several progrms. I take them through work and bought the CD learning tools for Access97. It is very good, shows you right on your screen all the key strokes. Very easy to learn

[MitchS]

Hey Hammertime & Tom,

Thanks guy's for the input. I don't know if my brain can take anymore tonight, and the sad thing is i have not gotten too far in accomplishing what i want.

sjk,

I finally figured out your formula, but i don't understand what it is returning. I did only 3 columns-won,odds,and another one. Expr1 your formula returnes -1 if the horse lost and a number that coresponds to the odds if the horse won. EXAMPLE; Last entry was a winner with 10.8 odds and the Expr1(your formula) returned 10.8000001907


Mitch

[linrom1]

I'm not a rocket scientist, but, in my Finance 101 class, I was taught that:


ROI=(Win-Bet)/Bet

[sjk]

Mitch,

You are looking at it race by race. To get the average (which gives the ROI) use the totals button (looks like sigma) and select average in the total row. You will need to make sure that you don't have any other fields listed as "group by" or the averaging will not be over everything.

[MitchS]

Thanks for all your help sjk. I still can't find the "Total" button. By the way, what is a "Sigma" and what does it look like?

I did find the Pivot Table View, where you can sum up any column you want. At least using the Pivot Table and the summed column values, along with a hand held calculator, will get the job done.

I know there is a better way. I would like to figure out how to do the calculations in the Reports view.


Mitch

[sjk]

Sigma is a Greek letter. It looks a bit like an E with diagonals instead of a bar in the middle. You will find it near the middle of the row of symbols near the top of the query design view.

[Jeff P]

I know there is a better way. I would like to figure out how to do the calculations in the Reports view.

The easiest way (my opinion) for a non-programmer to do this inside an Access Report is to define the value of each visual element (textbox/label - you have to draw these on the report itself) inside the Page Footer and/or Report Footer section using a simple formula. Do a Google search for phrases like "Access Reports Tutorial" and you can find thousands of pages that are similar to:

http://databases.about.com/library/weekly/aa122400c.htm

or

http://www.fontstuff.com/access/acctut14.htm

If there's something you want to do using Access (or any programming platform for that matter) chances are that somebody else has already figured out how to do it and created a web page somewhere that shows how.

[MitchS]

Jeff,

Thanks for the information and tips. Were getting this Access thing down, slowly but surely. It really can do amazing things and i have only scratched the surface.


Mitch

[hurrikane]

ummm....mitch,

what is the current income potential of your first born.

shoot me an email..maybe we can deal

[ranchwest]

Originally posted by linrom1
I'm not a rocket scientist, but, in my Finance 101 class, I was taught that:


ROI=(Win-Bet)/Bet

ROI is Return on Investment.

Return=won
Investment=bet
ROI=won/bet

It is really as easy as it could possibly be.