MTD and YTD calculation

Posted on 2013-11-24
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Expert Comment

by:Dale Fye
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.
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.


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 48

Expert Comment

by:Dale Fye
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 2000 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

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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