Year To Date column

[treva31]

Hi,

I have a table like the one attached (simplified from the original).
If I make a query based on it, I cannot figuire out how (or if?!) I can get the last two columns to calculate:

YearToDate and FullYear

The CurrentYear and CurrentPeriod can be stored in a table called CurrentDate.


Ideally I would like it to be compatibale with an SQL server which I hope to host it on later.

Any ideas would be welcome.

Thanks,
Trevor.

 

[Guus2005]

Next time if you provide an Access database instead of an Excel spreadsheet it would be much easier to answer your question.

SELECT Sheet1.Period, Sheet1.Budget_Dollars, Format(DSum("Budget_Dollars","Sheet1","Period<=" & [Period]),"0,000.00") AS YTD, Format(DSum("budget_dollars","sheet1"),"0,000.00") AS FullYear
FROM Sheet1
GROUP BY Sheet1.Period, Sheet1.Budget_Dollars, Format(DSum("Budget_Dollars","Sheet1","Period<=" & [Period]),"0,000.00"), Format(DSum("budget_dollars","sheet1"),"0,000.00");

The above query does what you want. It calculates YTD, based on the sum when the period is smaller or equals to the current one.

If your domain functions are getting slow, please investigate the TLOOKUP alternatives.
Search for TLOOKUP.

Enjoy!
Enjoy!

 

[Guus2005]

Next time if you provide an Access database instead of an Excel spreadsheet it would be much easier to answer your question. Sheet1 imported into an Access database:

SELECT Sheet1.Period, Sheet1.Budget_Dollars, Format(DSum("Budget_Dollars","Sheet1","Period<=" & [Period]),"0,000.00") AS YTD, Format(DSum("budget_dollars","sheet1"),"0,000.00") AS FullYear
FROM Sheet1
GROUP BY Sheet1.Period, Sheet1.Budget_Dollars, Format(DSum("Budget_Dollars","Sheet1","Period<=" & [Period]),"0,000.00"), Format(DSum("budget_dollars","sheet1"),"0,000.00");

The above query does what you want. It calculates YTD, based on the sum when the period is smaller or equals to the current one.

If your domain functions are getting slow, please investigate the TLOOKUP alternatives.
Search for TLOOKUP.

Enjoy!
Enjoy!