Query Averaging Help Needed

[assassin4213]

Ok, here's the scoop. I have the following record setup for tracking shipments:

[Shipment_ID_#], [Date_Loaded],[primary_silo], [secondary_silo], [2_silos]

I need to retrieve average values from another table for certain chemistry values based on the [Date_Loaded] and whether or not one or two silos were used when loading the shipment (as indicated by the [2_silos] checkbox).

Scenario #1 - one silo

I need average values of the last four samples based on the [date_Loaded] and the appropriate silo.

Scenario #2 - two silos

I need average values of the last two samples from each silo based on the [date_loaded], [primary_silo], and [secondary_silo].

Would anyone have any thoughts on this? I've tried crosstab and select queries, but I can't get anything to work.

My ultimate goal is to have a form where users select the shipment and the appropriate chemistry averages automatically populate the data fields.

Any help is greatly appreciated.

Thanks,

Greg

 

[gemma-the-husky]

sort an access query by date descending probably

then right click the query pane, and select properties -
one option here is top values

there is top 5 and a number of others , i am not sure whether sql would let you edit this to top 4

then you can add the values and average them in the reslting record set

- see what the sql looks like as a start anyway