Hi all!

As I am kind of newbie in Access I would appreciate if someone could help me with next query.
I have two series of data a and b (in one table) and I have to calculate (a-b)/((a+b)/2) for all records and then get the average of this data. I spent hours for this easy equation which would only take me a few seconds in excel so I think it wont take much more to Access expert.

I use Access 2003.

Thanks in advance.

I have two series of data a and b (in one table) and I have to calculate (a-b)/((a+b)/2) for all records and then get the average of this data.If a and b are both fields, then create two new fields in your SELECT query, like this:SELECT ((table.fieldA - table.fieldB) /
((table.fieldA + table.fieldB) / 2)) AS [newfieldname],
Avg([newfieldname]) AS [Average of equation field]...This will give you the average in the entire column of the query, so it might be best to just calculate the the first field values and then get the Average of the field as one number in a form text box or something like that.

Georgje,

Unlike Excel the table in Access is virtually a non active thing.....just holds the data.

To do what you want first make a Select Query. Just click on Queries and then New and a box will pop up and from that click Design View. That will open a screen that is grid like and also a box showing all your tables (or queries)

Pick the table with that has the two fields and click on the table name and then Click Add and then close the Show Table box.

You will see the table above the grid. Now drag down the fields a and b to the Field row.

In the next blank column on the Field row you create your new field with a colon at the end of the name. After the colon you type your formula.

Calcaandb:([a]-)/(([a]+[b])/2)

When you reference field names in Access you put them between [ ]. Text is "xyz"

Close the query to save it. Now open the query and it will have 3 colums consisting of [a] [b] and [Calcaandb] and the the last field will have the result of the calculation.

To get the average (this is the longer way but is step by step) you make another query and this query will be based on the query you just made. Hence that Show Table that pops up is for both tables and queries.

On your new query you drag down Calcaandb to the Field grid. Up on the toolbar you will see that symbol like an E (like Excel), you click on it and below the Field name you dragged down you will Group By. If you click on it you will see a list functions such as Count , Max and of course Ave. Select Ave. Close and save the query. Open the query and one row will appear and that will show the average.

As [B]ajetrumpet mentioned you can also get the average by using a form based on the first query. In Access you can add to a form unbound text boxes. They look like a field but do not get their data from the underlying table/query. A formula can be entered that would reference your new firld name [Calcaandb]

Great guys

It finally works I`ve used Mike`s suggestion and did it like this

SELECT Query1.BID, Query1.OFR, (([OFR]-[BID])/(([OFR]+[BID])/2))*100 AS SPREAD
FROM Query1;

but I understand ajetrumpetnow`s logic as well. Many thanks to both of you bcs I`ve learnd much more from your posts than from 20 hour Access course :)

I`ve learnd much more from your posts than from 20 hour Access course :)Wow! That sure says a lot about us, eh? :D :D