Using Access 2007: Can I get the weighted average on the report??

[Bryan]

I am using Access '07. I have a query created that has my average crop yields
per farm and field. When I create a report, I group the farms together. I
would like to have the average yield by farm on the report, but it does not
use the weighted average, and some fields acres are larger than others which
would mean it has more bearing on the overall average.

For example:

Home South; Yield 180; Acres 100
Home North; Yield 125; Acres 30

My report would tell me the average was 152.5
When in all actuality was 167.3 using weighted average, which is the correct
way for this scenario. I just cannot figure out how to get the report to show
this way.

Hopefully I am explaining this clear enough. I am new to access, so any help
would be apprecitaed!!

Bryan

 

[Clif McIrvin]

(It's generally better NOT to start a new thread for the same problem
..... keeping things together a) makes it easier for the volunteers here
to "see" all the facts (a difficult proposition at best <grin>), and b)
makes the archives more useful for whoever comes along sometime in the
future wanting to solve a similar problem.)

My reply below...

I am using Access '07. I have a query created that has my average crop
yields
per farm and field. When I create a report, I group the farms
together. I
would like to have the average yield by farm on the report, but it
does not
use the weighted average, and some fields acres are larger than others
which
would mean it has more bearing on the overall average.

For example:

Home South; Yield 180; Acres 100
Home North; Yield 125; Acres 30

My report would tell me the average was 152.5
When in all actuality was 167.3 using weighted average, which is the
correct
way for this scenario. I just cannot figure out how to get the report
to show
this way.

Hopefully I am explaining this clear enough. I am new to access, so
any help
would be apprecitaed!!

Bryan

Assuming Yield and Acres are your fieldnames:

SELECT SUM(Yield*Acres)/SUM(Acres) AS WeightedAvg
FROM TableName
--
Clif
Still learning Access 2003



Now a bit from the previous thread:
Re: Can I get the weighted average for the table?

 

[Bryan]

Clif-

Thank you, I will do that from now on.

Would I accomplish what you said in the report designer or in the table
design?

I thought perhaps there was a way to edit the "Totals" function that I
selected in the report design to accomplish the weighted avearge?

For this project, I have an external link with a .dbf so I am only using
access to query and report on the single .dbf file.

Thanks for the help, sorry about how naive I am about this!

Bryan

 

[Clif McIrvin]

Clif-

Thank you, I will do that from now on.

Would I accomplish what you said in the report designer or in the
table
design?

From the query designer:

[quote from previous thread]
That is the entire query, in SQL view. Create a new query without
selecting
any table; select View... SQL; and edit this in, using your own field
and
table names.
--

John W. Vinson [MVP]
[/quote]

I thought perhaps there was a way to edit the "Totals" function that I
selected in the report design to accomplish the weighted avearge?

Not that I know of (but I'm fairly new at this stuff myself.)

After you create the new query in SQL View per John's directions (your
linked table will be the FROM TableName) you can switch to Design view
and add any other fields (columns) you need from your linked table. The
calculated field "Weighted Average" will give you an example of how to
create a calculated field -- you can include other calculated fields as
needed.

After your query definition results in the information you need, then
you build your report off of the query, not the table.

In general, define calculated fields in queries, not forms or reports,
because you can define the calculation ONE time and use the calculated
result in other queries, forms or reports as though you were getting the
value from a table. That way there is less opportunity for gremlins to
change the formula between different places where you used it..

Also, report sorting and grouping "ignores" any sorting done by the
source query, so there is no need to sort a query that is feeding a
report.

For this project, I have an external link with a .dbf so I am only
using
access to query and report on the single .dbf file.

Access is quite good at that! (And, provides a lot of powerful tools to
do it with.)

Thanks for the help, sorry about how naive I am about this!

Bryan

Nothing to be sorry about -- we all started somewhere! (And learn from
each other as we go along, as well.)

I seem to recall that you mentioned being new to Access -- here are some
links to excellent introductory materials:

(Thanks to John W. Vinson [MVP] for this info

"Access has a steeper learning curve than (say) Word or Excel; not
least, in order to make productive use of the program you have to
understand the theoretical basis of database design - a concept called
'Normalization'. It is very logical and not at all difficult once you
get the concepts down."

Here are some tutorials and other resources that you should find
helpful:

A free tutorial written by Crystal (MS Access VP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

Good Luck!