Solved

MTD and YTD calculation

Posted on 2013-11-24
16
1,539 Views
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.  

Thanks
MTD-YTD-testing.accdb
0
Comment
Question by:Kdankwah
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 1

Expert Comment

by:Dephault
Comment Utility
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?
0
 
LVL 21
Comment Utility
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
GROUP BY mtd.COSTCTR
HAVING (((mtd.COSTCTR) Is Not Null));

Open in new window

MTD-YTD-testing-HiTechCoach.accdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:Kdankwah
Comment Utility
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.

Thanks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
CostCtr:
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 [].
Category:
Segment:

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"
0
 
LVL 21
Comment Utility
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)?
0
 

Author Comment

by:Kdankwah
Comment Utility
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

Thanks
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
Comment Utility
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
MTD-YTD-testing-HiTechCoach2.accdb
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Kdankwah
Comment Utility
You are right on the money.  Should I store the entries in the query or I can do it in the report.

Thanks
0
 
LVL 21
Comment Utility
Great.

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?
0
 

Author Comment

by:Kdankwah
Comment Utility
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.

Thanks
0
 
LVL 21
Comment Utility
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.
0
 

Author Comment

by:Kdankwah
Comment Utility
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.  

Thanks
0
 
LVL 21
Comment Utility
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.
rptTotalsByPeriod1.pdf
0
 

Author Comment

by:Kdankwah
Comment Utility
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.

Thanks
0
 
LVL 21
Comment Utility
Great job figuring it out.

 Have a happy thanksgiving.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 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

17 Experts available now in Live!

Get 1:1 Help Now