Dynamic YTD Total

[wildsyp]

Hi,

I hope somebody can help me.

I need to create a query which will dynamically calculate a YTD Total from 12 separate monthly columns based on a month selected by the user on a form.

For example, if the user selects August 2013, the query will return the total individual columns from Jan-Aug and so on.

In addition, I would also like the query to only return the monthly results of the month selected (August), so I will have three columns in the output (ID, current month total, current month YTD).

I hope this makes sense. Any help you could provide will be fantastic!

Thanks
Paul

 

[pr2-eugin]

wildsyp, so what is that you have done so far? How are your tables setup?

 

[wildsyp]

The data is made of of several columns of unique identifiers which link to other tables (which I join to other tables in the query), but the data I need to manipulate is made up of 12 separate columns (by month) in the raw table. The structure of the data isn't in the greatest format which doesn't help, but unfortunately it is from an external source, so I can't current change it.

So far, I am manually adding the columns together in the query like so:

SELECT [ID],
[01/08/2012],
Sum([01/01/2013]+[01/02/2013]+[01/03/2013]+[01/04/2013]+[01/05/2013]+[01/06/2013]+[01/07/2012]+[01/08/2012]) AS YTD
FROM TblMain;

This is far from ideal as would need manually intervention each time I run it.