Click here to Skip to main content
14,328,552 members
Rate this:
Please Sign up or sign in to vote.
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:
Please Sign up or sign in to vote.

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.
   
Comments
Maciej Los 14-Aug-19 6:34am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

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)




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