Creating an Average Formula

[80proof]

I have a query and would like to get the average of a few fields in my table. Then I would like to also have this same average minus the best and worst number in the field. Is creating a form the best way to go as far as manipulating data in a query to get certain values?

Is it also possible to count the number of items in a field and separate them into those >0; =0; and <0?

Any suggestions would be greatly appreciated. Thanks.

 

[lespaul]

No you don't need to create a form, just a query.
Here are some general SQL statements that show how to get AVG and COUNT, but without some example data of exactly what you want, it's a little difficult to give you specific help.

to get an average:

SELECT SomeField1, AVG(SomeField2) FROM SomeTable GROUP BY SomeField1

To get a count:
SELECT SomeField1, COUNT(SomeField2) FROM SomeTable GROUP BY SomeField1

In order to get the >0 =0 and <0 I would probably do something like this (based on your OP - if I could see exactly what you're trying to do, I might have a different suggestion):

SELECT ">0", COUNT(SomeField) WHERE AnotherField > 0
UNION
SELECT "=0", COUNT(SomeField) WHERE AnotherField = 0
UNION
SELECT "<0", COUNT(SomeField) WHERE AnotherField < 0

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[80proof]

Thanks for your help, sorry i left out some important info regarding what the columns relate to.

The field i would like an average of is titled "Market Cap" and contains numbers ranging from $50,000 to $20,000,000,000.

The fields I would like to count entries greater than, equal to and less than zero are called "% Change" and are percentage colums ranging from -100.0% to 234,543.0%.

thanks for your quick response let me know if there is anything else i can provide you with.

 

[lespaul]

Are you trying to get information from the query or the table? Can you post the SQL of the query and a sample of the results of that query and what your new results should be?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[80proof]

I am creating this access database because I had an excel spreadsheet with lots of data and want to be able to manipulate it easier and allow others to manipulate it as well, so i have never worked with access before.


The "market cap" field in the query comes from the underlying table using the following code:

Market Cap: ([Shares Outstanding]*[Closing])

the result of which is displayed in the query. It is the result displayed in the query that I would like an average of. I would like to have the result displayed either as a header or footer so it can be easily viewed by someone manipulating the data without scrolling to the end of the fields to find the answer. does this make sense? sorry if this is unclear.

It would look something like this:

Market Cap
10,000
20,000
30,000
40,000
AVG25,000

thanks for all of your help

 

[lespaul]

So you need to add this average to your EXISTING query (if so, if you'll switch to the SQL view of the query and post that...) or you want to have a NEW query that calculates the average?

If you want the average in the same query, you'll have to do something like :

AVG([Shares Outstanding]*[Closing])

In most circumstances you can't use an alias (like Market Cap) within the same query, you always have to refer to the actual calculation.

Additionally, check out Thread701-1258387 for information how aggregate functions (like SUM, COUNT, AVG) are GROUPED and Thread701-1262760 for an explanation of the HAVING clause which Access misuses regularly.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[80proof]

Here is the SQL view, sorry I didn't get it up here sooner:

SELECT [tblPIPE Deal Database].Date, [tblPIPE Deal Database].Issuer, [tblPIPE Deal Database].Ticker, [tblPIPE Deal Database].[$ Raised], [tblPIPE Deal Database].Structure, [tblPIPE Deal Database].[Shares Outstanding], ([Shares Outstanding]*[Closing]) AS [Market Cap], ([$ Raised]/[Market Cap]) AS [% MC Raised], [tblPIPE Deal Database].Closing, [tblPIPE Deal Database].[30 Days], (([30 Days]-[Closing])/[Closing]) AS [% ? 30], [tblPIPE Deal Database].[90 Days], (([90 Days]-[Closing])/[Closing]) AS [% ? 90], [tblPIPE Deal Database].[180 Days], (([180 Days]-[Closing])/[Closing]) AS [% ? 180], [tblPIPE Deal Database].[1 Year], (([1 Year]-[Closing])/[Closing]) AS [% ? 1 Y], [tblPIPE Deal Database].[1 1/2 Years], (([1 1/2 Years]-[Closing])/[Closing]) AS [% ? 1 1/2 Y], [tblPIPE Deal Database].[2 Years], (([2 Years]-[Closing])/[Closing]) AS [% ? 2 Y]
FROM [tblPIPE Deal Database]
WHERE ((([tblPIPE Deal Database].Structure) Like ([Deal Structure]) & "*") AND ((([Shares Outstanding]*[Closing]))<=75000000))
ORDER BY ([Shares Outstanding]*[Closing]) DESC;

I originally wanted to have this same query average the columns, but it might be more functional if i create a new query with the ability to average the existing query.

Thanks for helping me!