X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Month To Date Average Per Day - Function, Access 2016    
post Nov 18 2017, 03:08 PM

Posts: 154
Joined: 21-August 12

Hello All,

I'm looking for a way to calculate a year to date average by day.

In this post, http://www.UtterAccess.com/forum/index.php...c=1719479"], I found a query function that worked for the month to date calculation:

Between DateSerial(Year(date()), Month(date())-Abs(Day(date())=1),1) And Date()-1

I would be very appreciative if someone could give an idea how to convert this to a YTD calculation.

Thank you for any insights,
Go to the top of the page
post Nov 18 2017, 04:29 PM

UtterAccess VIP
Posts: 8,088
Joined: 25-October 10
From: Gulf South USA

Hi Grace: I think we'll need more information.

What do your records look like that you will use to calculate the averages?
What value(s) are you averaging?
Do you want averages for all days in the period, or weekdays, or workdays? If workdays, what does your workday calendar look like?
Edit: Or do you want something like average sales, where each record is a sale, within the time period?

The calculations, and query, will flow from these answers.

The date range calculations are easy, but they may depend on what the data look like.

And it would be helpful to have the table from which you will draw the records (in a db, no sensitive data, zipped) plus the workday calendar if that applies.


Oh, and your link doesn't seem to take us anywhere... you might want to re-post that ...

"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
John Vinson
post Nov 18 2017, 08:32 PM

UtterAccess VIP
Posts: 4,182
Joined: 6-January 07
From: Parma, Idaho, US

A criterion to return all records to date this year would simply be

Between DateSerial(Year(date()), 1, 1) And Date()-1

This actually gives all records up to YESTERDAY, not today; leave off the -1 to get all records up to midnight last night, and make it +1 to get all records through the end of today (assuming your table field contains a time portion).

The Month to Date expression can be simpler:

Between DateSerial(Year(date()), Month(Date(), 1) And Date()-1

with the same advice about the -1.

John W. Vinson
Wysard of Information
Go to the top of the page
post Nov 26 2017, 04:30 PM

Posts: 154
Joined: 21-August 12

Thanks John and RJD!!
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th February 2018 - 06:47 PM