How to use Average Function in Access

[CC Brown]

I want to look at all winners and see what the average is for several rankings such as workout, K, HTR etc. How do I do that? When I use the Total icon to get the box and put in ave. nothing seems to happen.
Thanks for any help.

[Donnie]

CC--
Any field that is not a summary field (sum, count, max, etc.) must be left on Group By. Maybe you can go into the SQL view of your query and post it here? Much easier to troubleshoot that way!

[Donnie]

Ohhhh BTW...it is avg not ave!

[CC Brown]

I want to look at the strength of several factors example rWKSC and rKrat for fast dirt sprints with F1=1. I have nsurf=1 nDS=1 F1=1 and fin=1 then when I put average in the group by box for rKrat and rWKSC it shows the same number as rKrat and rWKSC. I want to get the average of the entire column. I want to see if rKrat of rWKSC or whatever is the strongest factor. This I would see by the lowest average rank.
Here is the SQL: Maybe I need to remove race and track so they won't be grouprd by that?

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nSURF, ALL_HX4.nDS, ALL_HX4.tPGM, ALL_HX4.rMLO, ALL_HX4.nMLO, ALL_HX4.rFr1, ALL_HX4.rEP, ALL_HX4.rFr3, ALL_HX4.rLP, ALL_HX4.rKrat, ALL_HX4.xFIN, ALL_HX4.xWIN, ALL_HX4.rWKSC, Avg(ALL_HX4.rWKSC) AS AvgOfrWKSC, Avg(ALL_HX4.rKrat) AS AvgOfrKrat
FROM ALL_HX4
GROUP BY ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nSURF, ALL_HX4.nDS, ALL_HX4.tPGM, ALL_HX4.rMLO, ALL_HX4.nMLO, ALL_HX4.rFr1, ALL_HX4.rEP, ALL_HX4.rFr3, ALL_HX4.rLP, ALL_HX4.rKrat, ALL_HX4.xFIN, ALL_HX4.xWIN, ALL_HX4.rWKSC
HAVING (((ALL_HX4.nSURF)=1) AND ((ALL_HX4.nDS)=1) AND ((ALL_HX4.rFr1)=1) AND ((ALL_HX4.xFIN)=1));

[Donnie]

CC--
You say it shows the same number for both fields....is that in each of the two different fields you want, even across tracks? I would start small. I started with these in my group by (in this order) Trk, Surf, Dist, Fr1, and FIN, then added the two fields of Avg of rKrat and Avg of rWKSC. It all apears to be working fine....one thing...Access will parse and group in the order you place the grouped fields...in your example you grouped first by TRK then DATE then RACE, which means your avg will be the avg for that day! You need to remove some of those grouping levels. I believe you may be getting too restrictive.

[CC Brown]

I hace taken everything out except F1=1 Fin=1 rKrat and rWKSC. I then put the average in the group by box that is opened when I hit the Totals icon. Now I have 109 rows instead of 2009. But I still don't see an average for the column. I must not understand how this function works. Do I need to to a sum of the column then divide by a count of the number of rows?

[Donnie]

I'll try to explain a little better than what I did above...sometimes I have a hard time understanding what I say!

First let's start simple. If I wanted the average of all nFR1's in my table, I would start a new query, change to a Total Query, add the nFR1 field to the Design grid and run the query. In my answer set I will get one number...that number being the average of all of the nFR1's in my table. Now if I add the field tTRK to the grid, I will have the average of all nFR1's in my table execpt that the nFR1's are now averaged by Track. I will now add one more field to my Total query. This is gonna be the nDIST field. What the query now shows me is the average nFR1's broken down by Track first, then within each track by the respective distances. It is important to understand that the order by which the non-summary fields are arranged are the grouping order in which the data is returned. By adding too many fields you may be asking Access to put your data into too small of groups. This was the problem earlier when you had Trk, Date, Race. It first grouped your data into track groups. Then within each track group it further broke your data down into Date Groups (ei: one day is one group), and then within each Date Group into each race. You were adding too many grouping levels.

So in order to achieve what you are looking for, think of the order you want them grouped. Such as:

Trk(groupby), Dist(groupby) with a criteria of <8 to see just sprints, rFR1 (average), nWKSC (average).

This would give you the average of rFR1 and nWKSC broken down first by Track then by distance.

Hope this helps!

[Donnie]

Oops.....just reread your post and it looks like you want to run this against just Fr=1. Develop a query to find that data first! Such as run a query to find all winners and their rFR1 and nWKSC. So you would have TRK, DIST, xFIN =1, rFR1 and nWKSC. Save this query. Do the previous post's steps now on this query instead of on a table!

Sorry....didn't see you wanted to test very specific data!

[CC Brown]

OK, I got it to work. Thanks Donnie!
I created a query to get all the records I wanted(F1=1, nDS=1, nSurf=1) . Then I saved that query like you said. Then I could use all the factors I wanted by putting in average. I should have recognized this since the box that pops up says "group by" but the help function in Access must be written in a language I don't understand.
Thanks Again!

[MikeDee]

As always there are multiple ways to get something done in access.

One of the problems with using the math functions in a query is that you have to group your results on something. For us that normally means track surf distance, class, etc.

I have found an easy way to force access to group on everything in your query is to group on Pace line. (xPL). The value of the PL field is the same for every horse (i.e. 5 if you are using pl5 in your export). So if you group on this, you get every race in your query without the need to group on some other field that is not the same for each horse.

Next you can use the where clauses to limit (or filter) the number of horses you are looking at. For example you can add where nsurf =1, where nds=1, for fast dirt sprints.

Once you have set the grouping and where clauses that you want to limit what is going to be averaged. You can put in multiple fields and average them. For example you can average rfr1 and rwksc and any other factors you want in the same query.

I have attached a sample of what the query design looks like in the attached.

[hurrikane]

Thanks Mike,

the PL grouping is a good idea, i hadn't used that before.

[CC Brown]

Thanks Mike. How long before you figured out this clever idea. That really makes it simple!

[MikeDee]

I don't know CC it has been a while now. It's one of those things, that after you stumble on it you think "that was easy how come it took me so long it figure it out?"

[Donnie]

Good answer Mike...never thought to use PL as the "equalizer"!