Current Month and Year-To-Date

[DSnipeFunk]

Hiya folks!

Heres the problem - would appreciate any help:

I have three tables related in a one-to-many relationship:

TABLE ONE: StatisticPeriod
TABLE TWO: StatisticProperty
TABLE THREE: StatisticRevenue

Table One is where I insert the Month and Year.
Table Two is where I insert the Property (in this scenario it is a hotel that has several properties/buildings in the country).
Table Three is where the Revenue Category and Revenue Values go (i.e. Food, Beverage, Rooms, etc.).



So for each month/year/property, there is a revenue value for each type of revenue that the hotel generates:

What I need to achieve with this is three main types of reports (see below) - using the least possible number of queries and sub-queries - and I really have no clue where to start. Using an unbound form, the user will be able to select the month and year and property upon which the query/report will be based (the user can choose a specific property or all of them - the ALL has already been configured in the combo):

REPORT ONE (this example is if I select that I want 2011 statistics for all properties - it will give me the results for each month across the top of the page)

January 2011 February 2011 March 2011 etc......
20000 Euros 10000 Euros 5000 Euros etc......

REPORT TWO (if I select that I want February 2011 statistics - it should show February 2011 as well as February from the year before - as well as the current and past years to date up to February)

February 2011 February 2010 YTD 2011 YTD 2010
10000 Euros 1000 Euros 30000 Euros 3000 Euros

REPORT THREE (my database currently has three or four years of data, so this report would show the evolution over the last three or four consecutive years to date)

2011 2010 2009 2008
XXX Euros XXX Euros XXX Euros XXX Euros

Any advice?! Thank you very much!

[ajetrumpet]

i'm not gonna read through this, but what is the jist of it??

based on the title of your thread, apparently you need some functions that allow people to query certain time frames?? like year to date, for instance??

if that is the case, I have a completely database full of these examples and different methods to go about doing it.

[DSnipeFunk]

Thanks for your reply. So what are you suggesting?

[ajetrumpet]

i'm suggesting 2 things:

1) please answer my question or confirm that my assumption is right
2) if 1 is correct, tell me if you want to see my examples

[DSnipeFunk]

Ok - not exactly - try reading through my post to understand better what I am looking for Cheers!