423,680 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

field must calculate Maximum value between range of dates

P: 1
I have a table in access displaying a massive amount of rows of data over a course of a year (over 900k rows). I would like to create a query with columns showing the max value weekly, for example :
10/11/18 10/18/18 10/25/18
1 25 5 30
2 25 5 30
3 25 5 30
4 25 5 30

please help!
in the field i have the formula " Max[avggamersdaily] between #10/11/18# And #10/18/18# " and it just displays "0"
Jul 28 '17 #1

✓ answered by jimatqsi

The max function is only useful when you group your data correctly. You're going to need to group your data by week number. See this post on how to get a week number from a date. https://bytes.com/topic/access/answe...r-calculations

You'll likely need a subquery to return the week numbers in the data, then join that subquery to a query that returns a week number along with a Min(date) and Max(date). That way you'll have the dates you need along with the week number and whatever data you want to total.

Jim

Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,219
The max function is only useful when you group your data correctly. You're going to need to group your data by week number. See this post on how to get a week number from a date. https://bytes.com/topic/access/answe...r-calculations

You'll likely need a subquery to return the week numbers in the data, then join that subquery to a query that returns a week number along with a Min(date) and Max(date). That way you'll have the dates you need along with the week number and whatever data you want to total.

Jim
Jul 29 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.