I'm struggling to get an ms access query work in order to have the average of value based on 3 months if they do exist in the rows and no necessary consecutives.
I'm not able to calculate the way to test if for a specific month (included), I have 3 month in the rows, if so, limit the calculation only those three month, not less not more.
SELECT t1.DateDue, t1.[SupplierCode], iif(count(*)>3,avg(t2.ratioontime),0) AS avgratio
FROM otifdatatoexport AS t1
LEFT JOIN otifdatatoexport AS t2 ON (t1.DateDue>= t2.DateDue) AND (t1.[SupplierCode] = t2.[SupplierCode])
GROUP BY t1.DateDue, t1.[SupplierCode]
ORDER BY t1.[SupplierCode], t1.DateDue;
Any help will be greatly appreciated!
Arte
I'm not able to calculate the way to test if for a specific month (included), I have 3 month in the rows, if so, limit the calculation only those three month, not less not more.
- Here the query, I think I'm very close to the outcome I would like:
SELECT t1.DateDue, t1.[SupplierCode], iif(count(*)>3,avg(t2.ratioontime),0) AS avgratio
FROM otifdatatoexport AS t1
LEFT JOIN otifdatatoexport AS t2 ON (t1.DateDue>= t2.DateDue) AND (t1.[SupplierCode] = t2.[SupplierCode])
GROUP BY t1.DateDue, t1.[SupplierCode]
ORDER BY t1.[SupplierCode], t1.DateDue;
- based on the following table and the logic with outcome I would like :
Any help will be greatly appreciated!
Arte
Attachments
Last edited: