MTD and YTD calculation

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?

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));

Select allOpen in new window

MTD-YTD-testing-HiTechCoach.accdb

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.

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

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"

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

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

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

Thanks

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?

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

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.

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

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

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

Great job figuring it out.

 Have a happy thanksgiving.