Average Top 3 Values

[hotrodsue]

I enter Vehicle Tare Weight into a Table (named Tare), by Date and by VehicleID. Then I get top 3 (most recent) Vehicle Weights in query named QryRecentTare. Then I average the most recent 3 Vehicle Weights in query named QryRecentTareAverage.

This gives me the average of the 3 most recent Tare Weights by Vehicle. The result is used to calculate the total Truck (Vehicle) Load Weight. After entering test data, I'm running into a problem. Average of the 3 most recent Tare Weights will change as I input new dates and weights.

As I input Tare Weights for another date, I no longer have my initial Average weight. For example, I need the Average of the 3 Most Recent Weights (by Vehicle) as of June 1st and as of June 15th. My results are ONLY as of June 15th as it is the most recent.

Is there a way to query the results as of June 1st, even after input of data in future dates?

 

[Galaxiom]

Add the date field to the query and set the target date as the criterion.

For all vehicles before or equal to the date enter <=[querydate]

 

[hotrodsue]

Thanks so much, that worked great!