Skip to content
Advertisement

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:

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

The linking:

  1. tblWEIGHT.idPONR = tblPONR.ID
  2. tblSALES.idPONR = tblPONR.ID
  3. 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.

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:

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

Desired outcome read comments for AvgWeight

Advertisement

Answer

Consider:

Query1

Query2

Query3

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement