Access Average Problems Between Names and Dates

Question

I have a database that is more or less the following:

John Smith
Jan 1 2016
Sales $80
Jan 2 2016
Sales $100

Jane Doe
Jan 1 2016
Sales $85
Jan 2 2016
Sales $110

My report is setup to break on names and on dates (b/c in actuality there are more numbers than just sales, but I'm simplifying this question).
I need to find the average for a week, rolling 4 weeks, and month for each person. I can control the date range specified in the report using a form I made so I can get them to click a button that autofills a range of time based on today (a week would be the previous 7 days, etc).

I cannot however figure out how to make it average everything under a name. Using Avg, DAvg, and some "grouping" controls have not resulted in anything useable. DAvg got me the closest but it would average EVERYONE together instead of breaking each person by their name (which is "Employee Name" in the query).

Any help or ideas would be vastly appreciated! I don't care if it is a modification to the query language or the report itself as I'm versed in VBA, SQL, and Access to an extent. I came from doing things in Excel, so I'm no Access pro, but I'm great with Excel. Unfortunately that doesn't solve my problem here haha! Oh wait, that's not funny...doh!

Thanks everyone!

Gustav Gustav · Accepted Answer · 2016-03-24T15:15:49

It should follow this line:

Select Person, Avg(Sales), Format(yyyymm, [Date])
From YourTable
Group By Person, Format(yyyymm, [Date])

For weeks it gets a little more complicated as weeks cross New Year.

Access Average Problems Between Names and Dates

1 Answers