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: 9,253
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 ...
Go to the top of the page
John Vinson
post Nov 18 2017, 08:32 PM

UtterAccess VIP
Posts: 4,277
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.
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    19th December 2018 - 09:09 AM