Need help with Davg

[ARW17]

What I need to do is average [ratio] by [day] if [Use?] = "y".

I've read a bunch of Davg posts, but I can't get it to work with my data. Any help is appreciated!

My Data:

WorkDate	Day	Ratio	Use?
10/31/16	MONDAY	6.67%	n
11/7/16	MONDAY	6.22%	y
11/14/16	MONDAY	6.19%	n
11/21/16	MONDAY	6.10%	y
11/28/16	MONDAY	7.28%	Y
10/25/16	TUESDAY	6.35%	n
11/1/16	TUESDAY	5.81%	y
11/8/16	TUESDAY	6.25%	y
11/1/16	TUESDAY	6.22%	y
11/15/16	TUESDAY	6.59%	y
10/19/16	WEDNESDAY	6.27%	n
10/26/16	WEDNESDAY	6.30%	y
11/2/16	WEDNESDAY	6.30%	y
11/9/16	WEDNESDAY	6.61%	y
11/16/16	WEDNESDAY	6.13%	y
10/20/16	THURSDAY	6.16%	n
10/27/16	THURSDAY	6.16%	y
11/3/16	THURSDAY	6.03%	y
11/10/16	THURSDAY	6.20%	y
11/17/16	THURSDAY	6.60%	Y
10/21/16	FRIDAY	6.79%	y
10/28/16	FRIDAY	5.35%	y
11/4/16	FRIDAY	6.40%	y
11/11/16	FRIDAY	6.77%	y
11/18/16	FRIDAY	7.49%	Y

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

I want my final results to be:
Monday 6.53%
Tuesday 6.22%
Wednesday 6.33%
Thursday 6.25%
Friday 6.56%

 

[Joe4]

I would use an Aggregate Query instead, i.e.

SELECT Day, Avg(Ratio) AS AvgOfRatio
FROM TableName
WHERE [Use?]="y"
GROUP BY Day;

 

[ARW17]

I would use an Aggregate Query instead, i.e.

SELECT Day, Avg(Ratio) AS AvgOfRatio
FROM TableName
WHERE [Use?]="y"
GROUP BY Day;

Thanks! You led me down another road, which got me to the right answer. I haven't written any VBA in Access yet. Where would I put the code you provided?

The solution I used was:
Field: Day Ratio Use?
Total: GrpBy Avg GrpBy
Criteria: "" "" ="y"

 

[Joe4]

It is not VBA - it is SQL. When you create a query, you are using Query Builder. That is just a GUI Interface that builds SQL code for you behind the scenes.
If you create a new query, and change to SQL View, you can copy and paste the SQL code there and run the query.

But you can easily build this using the Query Builder by doing the following:
1. Add the Day, Ratio, and Use? fields to your query.
2. Add "y" to the Criteria row of the Use? field
3. Click on the Totals button (looks like a Sigma)
4. On the new Totals row that gets added under each field, change the value under the Ratio field from "Group By" to "Avg"
5. On the new Totals row that gets added under each field, change the value under the Use? field from "Group By" to "Where"
6. View your results

Aggregate queries are very useful and powerful. I would highly recommend brushing up on them. You can do a Google Search or check out YouTube for some tutorials.