Simple YTD query... Access newbie here

[h0raz0n]

Hi everyone

I have a set of data which is the total budget for the year and the monthly breakdown (ie. Budget full year, Budget Jan month-to-date, budget feb month-to-date,....., budget dec month-to-date). They are pulled from the server so I am unable to change how the data is presented.

The monthly values are calculated over a curve so I cannot just take the total and divide by 12.



I am trying to create a budget year-to-date column where it will sum up the monthly columns depending at the last completed month (ie. Month(Now())-1).

May I know how can I do that?

In excel I would simply use a Sum combined with Offset function.

Thanks.

[ranman256]

My query uses the form to get the date range,...
select * from table where date between forms!myForm!txtStartDate and forms!myForm!txtEndDate

Speed settings (ytd, montly, etc) set the date range quick, but the user can set it too.

[h0raz0n]

I do not have a date range, the data is presented with just the headers [BUD-01],[BUD-02],....[BUD-12] for the months and the rows listing the sales numbers.
It is not actual sales where I can make use of invoice dates.

[aytee111]

What does the data look like? Is it just the one record with the values you described? If so, add an if statement to each of the fields:

FebAmt: IIf(Month(Date()<2,0,[BUD-02])
MarAmt: IIf(Month(Date()<3,0,[BUD-03])
etc

Then in a second query you can add up all the fields: TotalAmt: JanAmt+FebAmt+MarAmt....

[June7]

Review http://allenbrowne.com/subquery-01.html#YTD

Your data structure appears not to be normalized.

A report may be best vehicle for you to do running sum.