Average columns based on value that I give

[William McNeill]

I want to create a form based of a query called "EURUSD." In the query I
have fields "Open," "Close," EWG," "OWG," and "HMG."
I want to put in text boxes and the above fields on the form. I would like
to be able to put in a value in TextBox1 and another value in TextBox2, and
get the averages of EWG, OWG, and HMG for the records where Open is equal to
TextBox1 and where Close is equal to TextBox2. I would like to be able to
type in values in TextBox1 and TextBox2 at will and retrieve the desired
averages.
Is this possible? Is there a better way? Thanks!!!

 

[Jeff Boyce]

William

It all starts with the data. You started with the form.

What's the underlying data structure?

Be aware that "averaging across columns" is a spreadsheet concept, not a
relational database (Access) concept.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[William McNeill]

Hi Jeff,

The underlying Data is is a lot of numbers imported from a Forex chart that
I use. I have a link to text report that the chart puts out. I have query
that converts the text to numbers, and formats them all to 4 decimal places.
There are about 972 records and they grow by 5 each week.
I think that I did not explain what I wanted to do very well. I don't want
to average across columns, I want the averages of each of the columns EWG,
OWG, and HMG, not average across them.
I have been using filters in the EURUSD query to select the Open value and
the Close value that I wanted, and then hit the sigma button and get the
averages of the EWG, OWG, and HMG columns that way. I think that I would like
to use a form instead because it is easier to type the numbers instead of
using the filters.
And if this works, I was going to add more "forex indicator" averages to the
same form. For instance, I was going too get the averages of EWG, OWG, and
HMG where Fields "High" and Low" were equal to TextBox3 and TextBox4, and put
this on the same form in a row below the Open and Close indicators.
Thanks again!!!

 

[Jeff Boyce]

William

If you are averaging "down" a column (i.e., a field in a table), that's what
Access does! Try creating a new query that pulls in that table, select the
fields/columns, then convert the query to a Totals query (see the 'sigma'
symbol). Use Avg() for the columns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[William McNeill]

I will give that a try. Thanks for your help. Have a great weekend!!