Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MTD and YTD calculation

Posted on 2013-11-24
16
Medium Priority
?
1,903 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
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?
0
 
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
GROUP BY mtd.COSTCTR
HAVING (((mtd.COSTCTR) Is Not Null));

Open in new window

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

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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

Thanks
0
 
LVL 49

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

Author Comment

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

Thanks
0
 
LVL 21

Accepted Solution

by:
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
MTD-YTD-testing-HiTechCoach2.accdb
0
 

Author Comment

by:Kdankwah
ID: 39675933
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
ID: 39676309
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
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.

Thanks
0
 
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.
0
 

Author Comment

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

Thanks
0
 
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.
rptTotalsByPeriod1.pdf
0
 

Author Comment

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

Thanks
0
 
LVL 21
ID: 39682789
Great job figuring it out.

 Have a happy thanksgiving.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 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