How to get a Weighted Average instead of an average of averages.

Many thanks to both slohombre and GustavBrock for providing such clear explanations.

One point possibly worth mentioning.

It would be better to display the total weight(32) & total acres (19.5 not 20) as e.g Double numbers rather than integer.

The weighted average is correctly calculated as 32/19.5=1.641 rather than 32/20=1.6.

However, when doing aggregate queries, Access may not correctly calculate expressions based on other calculated expressions. Sometimes you will need to run the query twice for the correct results

I would therefore recommend either calculating the weighted average as a separate query or using the actual expressions for both Total Weight and Total Acres in the Weighted Average expression.

SELECT 
    Week, 
    Yield, 
    Acres, 
    [Yield]/[Acres] AS YieldPerAcre, 
    Sum(Acres) AS TotalAcres, 
    Sum(Yield) AS TotalWeight, 
    Sum(Yield)/Sum(Acres) AS WeightedAv
    FROM Crop;