query

[Stacy]

I have a data table with multiple species and measurements of the species
with qualifiers.
Location Species Source Length Weight
CA A Water # #
CA A water # #
CA A land # #
CA B Water # #
CA B land # #
FL A water # #


I am trying to get a query that averages the length of each species by
source ie average length of all individuals of species A from the water ( 2
individuals) in CA and 1 in FL so that the query outcome looks like…..

Location Species A_water Length Species A_water Weight Species B
length
CA # #
#
FL # #
#

I posted a more simplified question about a query average but I don’t think
that is the problem. I have tried several if then statements and using
aggregate avg functions but they either give me error messages about multiple
aggregate functions or do not average the correct number of animals.

I am a biologist and apologize for my lack of computer/access knowledge any
help would be appreciated.

Thank you!

 

[ntc]

I believe all you need is a stand alone query; option it to aggregate (greek
E icon when in query grid design)

when you change it to aggregate it defaults all columns to "Group By" and
you would change the columns you want to Avg...

so it will group by specie and then avg those values...

you might have to play/experiment with whether/where the state column
appears or not...not clear on that point from your description......

 

[Stacy]

Thanks for the suggestion, but I have done that already. It does work that
way but I get a table that looks like...

Location Source Species Length avg Weight avg
CA Land A # #
CA Water A # #
CA Land B # #
CA Water B # #
FL land A # #
FL Water B # #

Creating mulitple entries for the same locations, with all of the possible
combinations as seperate rows instead of columns. What I would like is a
table that looks like ....

Species_A_Land Species_B_Land Species_A_Water Location
Length avg Length Avg Length Avg
CA # # #
FL # # #

Sorry that wasn't clear in my first post, my table was not formatted
correctly.

Thanks.