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!

Have you tried using a Crosstab query?

I have this example:
Dynamic Report based on a Crosstab query

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Thank you for your reply! Could you give me a heads up on how to start creating the crosstab for the database I am creating? I have the dates listed like this:

31.01.2011
28.02.2011
31.03.2011
30.04.2011

For each month I have RevenueCategory field and RevenueValue field so I need the crosstab to give me the following:

Jan Feb Mar ....... Dec
Food (RevenueCategoryField) X Euros X Euros X Euros X Euros
Beverage (RevenueCategoryField) X Euros X Euros X Euros X Euros

I am sorry - I know how this is done for simpler crosstabs, but dont know how to translate the dates into months/years and to have the months listed on the top.

Thank you!

Ok folks! I am really close now! :) The following crosstab query gives me what I need but for all years - how can I make it select only the chosen year??

TRANSFORM First(qryCompleteList.RevenueValue) AS FirstOfRevenueValue
SELECT Year([MonthEnding]) AS STYear, qryCompleteList.RevenueCategory
FROM qryCompleteList
GROUP BY Year([MonthEnding]), qryCompleteList.RevenueCategory
ORDER BY Year([MonthEnding])
PIVOT Format([MonthEnding],"mmmm");

I tried the following:


TRANSFORM First(qryCompleteList.RevenueValue) AS FirstOfRevenueValue
SELECT Year([MonthEnding]) AS STYear, qryCompleteList.RevenueCategory
FROM qryCompleteList
WHERE (((Year([MonthEnding]))=Year([Forms]![frmSelectParticulars]![cboSelectMonthYear])))
GROUP BY Year([MonthEnding]), qryCompleteList.RevenueCategory
ORDER BY Year([MonthEnding])
PIVOT Format([MonthEnding],"mmmm");

and also the following:


TRANSFORM First(qryCompleteList.RevenueValue) AS FirstOfRevenueValue
SELECT Year([MonthEnding]) AS STYear, qryCompleteList.RevenueCategory
FROM qryCompleteList
WHERE (((Year([MonthEnding]))=Year([Forms]![frmSelectParticulars]![cboSelectMonthYear])))
GROUP BY qryCompleteList.RevenueCategory
ORDER BY Year([MonthEnding])
PIVOT Format([MonthEnding],"mmmm");

but it gives me an error saying that the MS Office Database Engine does not recognise [Forms]![frmSelectParticulars]![cboSelectMonthYear] as a valid field name or expression. It is typed in perfectly - no mistakes so I am not sure where I am going wrong?

Thank you!!!

Did you create the parameter in the query?

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

I am sorry I didnt understand your question. Do you mean if I placed that SQL text in a query? If so, then yes I did.

Not the same thing.

If you will look at the example I gave you , open the query OrderDeatilsExtended in design mode. In the gray area next to the tables (above the grid) right click and select paramteres. This will show you what parameters are and what you need to set up.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Ah ok! Now I understand :)

I did like you asked but the parameters list was empty in both versions of the database that were included in the zip file. Can I use the parameters to give me only the months for a specific year? Cheers!

I told you to look in the incorrect query. Look in the other two queries that had the form references are criteria. They will also have the parameters defined

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015