14,328,552 members
Rate this:
See more:
I have a table in msaccess which has columns
Jan_CY | Feb_CY | Mar_CY |APR_CY.....

I want to calculate YTD(Year to day) Revenue using ms access query,

Ex: If current month is March then it should do some of Jan_CY+Feb_CY

Similarly if current month is April then it should so some of Jan_CY+Feb_CY+Mar_CY

Currently i am changing MS Access query manually every month which is repititive task for me,
can somebody suggest me good solution on this?.

Table Name: Customer

What I have tried:

Currently i change query in ms access manually based on the current month ex:

If current month is March then i write,
select Jan_CY+Feb_CY from Customers
Posted
Updated 14-Aug-19 0:52am

Rate this:

## Solution 1

To be honest, I'd probably start by looking at my data and working out if I'd stored it the most sensible way - while it is possible to do what you want with your current data structure, it's going to be very messy and unpleasant, where storing each of your existing columns in a separate table with a datestamp allows to you do "dynamic grouping" like that pretty simply by subtracting months from the datestamp.
Maciej Los 14-Aug-19 6:34am

5ed!
Rate this:

## Solution 2

First of all, please read carefully solution #1 by OriginalGriff[^]. I completely agree with His notes.

To abe able to achieve that you should UNPIVOT[^] data first.

```SELECT <SetOfColumns>, SUM(ValueToSum) AS SumOfSomething
FROM (
SELECT <SetOfColumns>, Jan_CY AS ValueToSum, SomeDateField AS Period
FROM YourTable
UNION ALL
SELECT <SetOfColumns>, Feb_CY  AS ValueToSum, SomeDateField AS Period
FROM YourTable
UNION ALL
SELECT <SetOfColumns>, Mar_CY  AS ValueToSum, SomeDateField AS Period
FROM YourTable
UNION ALL
SELECT <SetOfColumns>, APR_CY  AS ValueToSum, SomeDateField AS Period
FROM YourTable
-- till december
) AS T
WHERE (Month(T.Period) BETWEEN 1 AND Month(Date)-1) AND (Year(T.Period)=Year(Date))
GROUP BY ...```

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 RickZeeland 100 Richard MacCutchan 90 Patrice T 50 OriginalGriff 40 F-ES Sitecore 40
 OriginalGriff 2,313 Maciej Los 1,330 phil.o 958 Richard Deeming 590 Richard MacCutchan 401

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100