Completely Stumped - Calculating Year to Date

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
G

GoBrowns!

I am trying to create a DB where the user can input the month they are
looking for (listed as January, February, etc. right now) and get Cost Center
information.

My boss wants the report to look like this (ex: May data):

May '08 Plan Data May '08 Actual Data May '07 Actual Data YTD
Actuals/Plan

I can get all the monthly plan data, and even structure it so that the user
can choose the month..... how in the world can I calculate YTD data?!?!? I
have been looking everywhere. Please help! I would be happy to send more
info, if needed.

Thanks!!!!!
 
Can I use this subquery using month names or do I need to convert all my
month names to numbers? Dates?
 
Do you have fields named January, February, etc?

If so, you are going to have nightmares querying the database. You need a
*field* indicating the month and a row for each one. Repeating fields like
that are a basic mistake in normalizing data.
 
No, I have a field named "Month" and entries named January, February, etc....
is that okay?
 
I looked at your link, and I am unsure where my fields go........ here is
what I want to say:

If the Date is less than or equal to the date the user selected, sum "2008
Actual".

How does that work?!?

Thanks so much for all your help!
 
Perhaps a table with fields like this:
TheMonth Date/Time must be the of the month, format mmm-yy
CostCenterID Related to a table of cost centers.
Budget Currency
Actual Currency

You can then create subqueries along these lines:

You can then type an expression like this into the Field row in query
design:
LastYearBudget: (SELECT Sum(Budget) AS PriorBudget
FROM Table1 AS Dupe
WHERE Dupe.TheMonth = DateAdd("yyyy", -1, Table1.TheMonth)

YTDActual: (SELECT Sum(Actual) AS YTD
FROM Table1 AS Dupe
WHERE Dupe.TheMonth Between DateSerial(Year(Table1.TheMonth),1,1) And
Table1.TheMonth)
 
Back
Top