Last 3 months Avg Purchase 1

[molly]

This request seems harder than i first thought. I need to have the last 3 months average purchases from today's date (the date when i run the Query).

i have 4 fields: RawID, PO Date, Qty, Purchase$.

Is there an Access command that almost does this?

Your advise can point me in the right direction.

thank you. Molly

 

[lespaul]

maybe something like:
(typed, not tested, complete guess!)

SELECT Month([PO Date]), AVG(Purchase$) FROM tableName
WHERE [PO Date] BETWEEN Date() - 90 AND Date()
GROUP BY Month([PO Date])

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[molly]

Les - Homerun. this is great. The Last 3 months per RawID came in and your AVG command reduced the answer to just 1 row per RawID. i would have mistakenly made 2 qry's and your method did it in 1 qry.

thanks so much.
Molly

 

[lespaul]

great! I'm glad it worked....thanks for the star!

Les