Access sql Moving Average of Top N With 2 criterias

I have been searching the forum and found a single post that is a little smilair to my problem here: Calculate average for Top n combined with SQL Group By.

My situation is:

I have a table tblWEIGHT that contains: ID, Date, idPONR, Weight
I have a second table tblSALES that contains: ID, Date, Sales, idPONR
I have a third table tblPONR that contains: ID, PONR, idProduct
And a fouth table tblPRODUCT that contais: ID, Product

The linking:

tblWEIGHT.idPONR = tblPONR.ID
tblSALES.idPONR = tblPONR.ID
tblPONR.idProduct = tblPRODUCT.ID

The maintable of my query is tblSALES. I want to all my sales listed, with the moving average of the top5 weights of the PRODUCT where the date of the weight is less than the sales date, and the product is the same as the sold product. Its IMPORTANT that the result isn’t grouped by the date. I need all the records of tblSALES.

i have gotten as far as to get the top 1 weight, but im not able to get the moving average instread. The query that gest the top 1 is the following, and i am guessing that the query i need is going to look a lot like it.

SELECT tblSALES.ID, tblSALES.Dato, tblPONR.idPRODUCT, 
(
SELECT top 1 Weight FROM tblWEIGHT INNER JOIN tblPONR ON tblWeight.idPONR = tblPONR.ID
WHERE tblPONR.idPRODUCT = idPRODUCT AND 
SALES.Date > tblWEIGHT.Date 
ORDER BY tblWEIGHT.Date desc
) AS LatestWeight

FROM tblSALES INNER JOIN VtblPONR ON tblSALES.idPONR = tblPONR.ID

this is not my exact query since im danish and i wouldnt make sense. I know im not supposed to use Date as a fieldname.

i imagine the filan query would be something like:

SELECT tblSALES.ID..... avg(SELECT TOP 5 weight .........)

but doing this i keep getting error at max 1 record can be returned by this subquery

Final Question. How do i make a query that creates a moving average of the top 5 weights of my sold product, where the date of the weight is earlier than the date i sold the product?

EDIT Sampledata: DATEFORMAT: dd/mm/yyyy

tblWEIGHT
ID      Date     idPONR    Weight
1    01-01-2020     1        100
2    02-01-2020     2        200
3    03-01-2020     3        200
4    04-01-2020     3        400
5    05-01-2020     2        250
6    06-01-2020     1        150
7    07-01-2020     2        200

tblSALES
ID     Date    Sales(amt)   idPONR     
1   05-01-2020     30          1
2   06-01-2020     15          2
3   10-01-2020     20          3

tblPONR
ID    PONR(production Number)    idProduct
1             2521                    1
2             1548                    1
3             5484                    2

tblPRODUCT
ID     Product
1      Bricks
2       Tiles

Desired outcome read comments for AvgWeight

tblSALES.ID  tblSALES.Date  tblSales.Sales(amt)  AvgWeigt
     1        05-01-2020           30              123  -->avg(top 5 newest weight of both idPONR 1 And 2 because they are the same product, and where tblWeight.Date<05-01-2020)
     2        06-01-2020           15              123  -->avg(top 5 newest weight of both idPONR 1 And 2 because they are the same product, and where tblWeight.Date<06-01-2020) 
     3        10-01-2020           20              123  -->avg(top 5 newest weight of idPONR 3 since thats the only idPONR with that product, and where tblWeight.Date<10-01-2020)

Answer

Consider:

Query1

SELECT tblWeight.ID AS WeightID, tblWeight.Date AS WtDate, 
tblWeight.idPONR, tblPONR.PONR, tblPONR.idProduct, tblWeight.Weight, tblSales.SalesAmt, 
tblSales.ID AS SalesID, tblSales.Date AS SalesDate
FROM (tblPONR INNER JOIN tblWeight ON tblPONR.ID = tblWeight.idPONR) 
INNER JOIN tblSales ON tblPONR.ID = tblSales.idPONR;

Query2

SELECT * FROM Query1 WHERE WeightID IN (
    SELECT TOP 5 WeightID FROM Query1 AS Dupe WHERE Dupe.idProduct = Query1.idProduct 
    AND Dupe.WtDate<Query1.SalesDate ORDER BY Dupe.WtDate);

Query3

SELECT Query2.SalesID, Query2.SalesDate, Query2.SalesAmt, 
First(DAvg("Weight","Query2","idProduct=" & [idProduct] & " AND WtDate<#" & [SalesDate] & "#")) AS AvgWt
FROM Query2
GROUP BY Query2.SalesID, Query2.SalesDate, Query2.SalesAmt;

Advertisement

Answer