Year to date totals, month to date, week to date (1 Viewer)

[msquery]

Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

 

[crispbreeze19]

crude way

I am ok with queries. So here is a crude way of doing it. Subtract the date field that you have from Date() and then put in the criteria field <=365 for a year and then for month-to-date <=30 and the week-to-date <=7. For the month-to-date, since months vary in length, a Like state would be better in the criteria field so name the field MTD and put Like inthe criteria field then Date() hopefully this works

 

[Pat Hartman]

Each set of summaries requires a different domain of data. That means that they can't be calculated in a single query. You could do month and year in a single report but since neither months not years are evenly divisible by week (leap year), you would need a separate report/query to calculate the week information.

 

[msquery]

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

 

[Pat Hartman]

It is hard to see what you are doing wrong without seeing the SQL. It sounds like you are selecting fields that are preventing the aggregation.

Also - please do not start new threads on the same topic. If you feel you've waited long enough, add another post to the original thread to pop it back to the top of the list.

 

[raskew]

As pointed out before, you’ll need separate queries for each of the three totals. Example:

Source: QryNet
- OrderDate (date/time) – limited to current year
- Net (Currency)

QryYTD:

SELECT
    Sum(qryNet.Net) AS YTD
FROM
   qryNet
WHERE
   (((qryNet.OrderDate) Between DateSerial(Year(Date()),1,1) AND   Date()))

QryMTD:

SELECT
    Sum(qryNet.Net) AS MTD
FROM
   qryNet
WHERE
   (((qryNet.OrderDate) Between DateSerial(Year(Date()),Month(Date()),1) 
AND
   Date()));

QryWTD:

SELECT
    Sum(qryNet.Net) AS WTD
FROM
   qryNet
WHERE
   (((qryNet.OrderDate) Between DateAdd("d",-Weekday(Date()),Date()+2) 
AND
   Date()));

As written, qryWTD uses the current Monday as the starting date.

Once you have the three working, you could create a wrapper query by adding each of the previous queries and specifying YTD, MTD and WTD as the query's fields.

HTH - Bob

 

[revidium]

I'm looking for month to date that excludes today. I'm using this formula

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

This works great except for when it's the 1st of the month. Can anyone give some direction on how to handle the change in months when using this formula?

 

[namliam]

Kudoos for finding this old old thread , think it might have been better to start a new one with this one as a link or something...

Howabout:
Between DateSerial(Year(Date() - 1),Month(Date() - 1),1) And Date()-1

or:
Between Date() - 1 - Day(Date() - 1 ) + 1 and Date() - 1

 

[revidium]

Thanks nameliam. I tested the second one by rolling my date back to 6/1 and it seems to function properly.
I never know on forums so I always try to find a post that relates to what I'm doing.

 

[namliam]

Thanks nameliam. I tested the second one by rolling my date back to 6/1 and it seems to function properly.

Tested ??
Seems to.... ???



All good off course, you have no way of knowing I am the guru of guru's when it comes to dates or anything along those lines



OK, enough with the kidding and nonsence....

I never know on forums so I always try to find a post that relates to what I'm doing.

Digging around finding stuff is offcourse good, and providing links in you threads to what you have found or tried is good.
Resurrecting a thread thats 5 years old... I dont know, but your efforts are apprieciated non-the-less.