MTD and YTD calculation

Posted on 2013-11-24
Last Modified: 2013-11-27
I want to create a Month to date and a Year to date value summary calculations based on the category column of the attached MS Access database.  
Details of the database is that each month starting from July is numbered in Month_id column as 1 for July, 2 for August and so on to 12 for December.

What I need is the formula for the MTD and the YTD column.  

Question by:Kdankwah
  • 7
  • 6
  • 2
  • +1

Expert Comment

ID: 39673708
So should the MTD be a summary of everything in the month (Month ID)?

Or Do you want a running sum?

Also will there be a column indicating the year?
LVL 21
ID: 39673725
Something like this:

SELECT mtd.COSTCTR, Sum(IIf([Month_id]=[EnterMonth],[Value],0)) AS MTD, Sum(IIf([Month_id]<=[EnterMonth],[Value],0)) AS YTD
FROM mtd
HAVING (((mtd.COSTCTR) Is Not Null));

Open in new window

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39674282
1.  I'm a bit confused by:

"Details of the database is that each month starting from July is numbered in Month_id column as 1 for July, 2 for August and so on to 12 for December."

Do you mean 1 for July - 12 for June?

It also sounds like your YTD running sum will actually span two years (July - Dec and Jan - June)  which will exacerbate your issue some if you want the YTD value to compute July - June, instead of the standard Jan-Dec.

2.  What value do you intend to sort by in your running sum?  From your raw data, it appears that you probably want to sort by the ID field, since there is no other field that consistently increments within each Month.  

3.  Next, it appears that you want to store these computations in the table, is that correct?  This is generally (although not always) frowned upon as it creates an opportunity for those columns to be corrupted when a value in the [Value] column is updated.  It is generally considered better to create a query to provide you with the running sums when you need them.

It would help to know what you intend to do with this information before proceeding.

Author Comment

ID: 39674295
Yes, it will span 2 years, that is 1 for July and 12 for June.  Its a fiscal year (July to June) instead of calendar year (Jan - Dec) data.

2. This will be created in a report.  (sorry for not being clear). I will sort on the Month_ID field for each month.  The YTD will be the running sum and the MTD will be for only the month in question.

3. No it will be in a report.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39674350
Personally, I would recommend a date/time field in this table rather than the Month and month_ID fields.  You can create those values in your query.  By adding a data/time field [TimeStamp] to your table with a default of Now(), you would ensure that you know the sequence the records were added in.  It also looks like this is some sort of Expense Reporting, so you might want a second date field [ExpDate] to track when the expenditure was actually made.  Then, for your running Sum, you could sort by [ExpenditureDate] and [TimeStamp] to get a unique sequence.

So, your table structure would be something like:

ID: Autonumber
ExpDate: Date/Time formatted as short date
TimeStamp: Date/Time formatted as general date
Value: Currency  - I would consider changing the name of this field as [Value] is a reservered word and every time you use it you will have to wrap it in square brackets [].

As much as I would recommend changing your table structure, you can create the report you are looking for without adding a "running sum" to your query or table; simply do it in your report.  To do so:

1.  Create report, add a Group on (Month_ID), and a sort on (ID)
2.  Add all of your desired fields to the report
3.  Copy the Value textbox and label controls twice
4.  Change the label names of these new controls to MTD and YTD
5.  For the MTD textbox, set the Running Sum property (Data tab) to "Over Group"
6.  For the YTD textbox, set the Running Sum property to "Over All"
LVL 21
ID: 39675294
It looks like you may be importing this data from Excel.  Is that correct?

Will the data the data in the table only be for one fiscal year?  Currently your table will only handle a single fiscal year.

It would help to have more details on exactly how the report will look. Will it be grand total for the month with all cost ctr added together)?

Author Comment

ID: 39675413
Yes its coming from excel. The report will break on costctr with each MTD and YTD for each costctr.   I did try the over group and overall feature its kind doing a running  sum based on the whole database instead of  basing it on the costctr.  

The report should look like this

Costctr       Category      Mtd              Ytd

7607      Office      22222      44444
7607      Meeting      22222      33333
7607      Telephone      33      434
7608      Office      22      34
7608      Meeting      23      55
7609      Office      555      666
7607      Travel      777      333

LVL 21

Accepted Solution

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39675885
I got the feeling this was imported  from Excel by column names  and blank rows.

Every MTD report I have every created or seen has always been for a single period. Even if it has YTD totals. That is why my examples ask for the Fiscal period when it runs.

If you enter period 1 it will run for July. The MTD and YTD will be the same since it is the first period of the fiscal year. If you enter period 2 the MTD totals as for period 2 only and the YTD totalls are for the start of the fiscal year to the end of  period 2.  That is the normal way I see MTD with YTD totals.   The YTD totals are the MTD plus all the MTD totals from the previous periods in the same fiscal year.

See the attached report
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 39675933
You are right on the money.  Should I store the entries in the query or I can do it in the report.

LVL 21
ID: 39676309

Based on that I created a working report exactly like your example except I included  cost center totals.

Should I store the entries in the query or I can do it in the report.
Umm ... as far as I know there is nothing to store.

Did you look at the report in the example I attached?

Author Comment

ID: 39676478
Looking at your report, its exactly what I wanted.  Now to top it up a notch, if say I want to show current year and last years info side by side can I do that?  Previous info is in the same format as the current one.

The other thing, I will have to add in a budget for each category, how would I do that also.  Please help me.

LVL 21
ID: 39676523
Yes everything you want can be done in Access.

They key to success with this will be in getting the table set up correctly to store more that one years worth of data.

You said:
Looking at your report, its exactly what I wanted.
That is great. That would indicate it is time to close this question and start a new question with one of your newly added wants.

Author Comment

ID: 39681517
Follow-up question to the "TheHiTechCoach"

The first YTD calculations should be the same as the MTD but its not doing that, what do you think it is.  

LVL 21
ID: 39682548
TIP: Make sure you are in Print Preview mode!  I forgot to turn off Report view.

The first YTD calculations should be the same as the MTD ...

The MTD and YTD will only match for Period 1. I have attached a PDF for Period 1.

Author Comment

ID: 39682628
My bad.  I had the Month_id set to text so it was not doing the calculations based on numeric values, its working now since I changed it to numeric.  Have a happy thanksgiving.

LVL 21
ID: 39682789
Great job figuring it out.

 Have a happy thanksgiving.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now