How do I dynamically sum columns in ms access

Question

0.00/5 (No votes)

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 13-Aug-19 22:37pm

sunil mali

Updated 14-Aug-19 0:52am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2019-08-13T23:16:00

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 · Accepted Answer · 2019-08-14T00:53:00

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.

SQL

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

How do I dynamically sum columns in ms access

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0