Hiya folks!

I am having difficulty creating some business statistics reports - it seems very simple but I just can't figure it out. Here is what I am trying to do:

I have the following tables:

TABLE 1
Field 1: PrimaryKeyField
Field 2: Date (short date format)

TABLE 2
Field 1: TotalSalesForTheMonth (Euros)
Field 2: TotalNumberOfClientsForTheMonth (DoubleNumber)
Field 3: FieldToLinkToTable1PrimaryKey

My actual database has many more tables that are related to TABLE 1 and basically it is a database where I introduce numerous monthly business statistics. The below is an example of what the data would look like (business stats for the first three months of 2009 and 2010):

Date: 31-01-2010
---TotalSalesForTheMonth: 100
---TotalNumberOfGuests: 10
Date: 28-02-2010
---TotalSalesForTheMonth: 250
---TotalNumberOfGuests: 15
Date: 31-03-2010
---TotalSalesForTheMonth: 500
---TotalNumberOfGuests: 35
Date: 31-01-2009
---TotalSalesForTheMonth: 10
---TotalNumberOfGuests: 1
Date: 28-02-2009
---TotalSalesForTheMonth: 25
---TotalNumberOfGuests: 2
Date: 31-03-2009
---TotalSalesForTheMonth: 450
---TotalNumberOfGuests: 4

The difficulty I am having is in creating three different types of reports - I have an unbound form in which a user will select the month he wants the report for):

REPORT 1 (data by month for a single year)

--------------------------------------January 2010--------February 2010--------March 2010
TotalSalesForTheMonth----100----------------------250-----------------------500
TotalNumberOfGuests-------10-----------------------15-------------------------35

(I need the months on top and the business statistic title on the left - not the other way around)

REPORT 2 (data for specific month and year-to-date for a specific year)

--------------------------------------March 2010--------Year-To-Date 2010
TotalSalesForTheMonth----500--------------------850
TotalNumberOfGuests------35----------------------60

REPORT 3 (month of this year and same month of last year)

--------------------------------------March 2010--------March 2009
TotalSalesForTheMonth----500--------------------450
TotalNumberOfGuests------35----------------------4

The idea appears so simple but I have not been able to figure it out - tried playing with PivotTables and CrossTab Queries, but nothing I try seems to work. Maybe the way I have structured my tables is not correct? Maybe the way I am using the date field is not correct? Can anyone guide me in the right direction?

Thank you very much!

I managed to create the first report!

I first created a UNION QUERY:

SELECT "NumberDaysMonth" AS Category, StatisticDate AS MeasurementDate, NumberDaysMonth AS Measurement
FROM tblStatisticDate INNER JOIN tblStatistics ON tblStatisticDate.StatisticDateID = tblStatistics.StatisticDateID

UNION SELECT "NumberSeatsAvailableRestaurantMeal" AS Category, StatisticDate AS MeasurementDate, NumberSeatsAvailableRestaurantMeal AS Measurement
FROM tblStatisticDate INNER JOIN tblStatistics ON tblStatisticDate.StatisticDateID = tblStatistics.StatisticDateID

UNION SELECT "NumberSeatsAvailableTerraceMeal" AS Category, StatisticDate AS MeasurementDate, NumberSeatsAvailableTerraceMeal AS Measurement
FROM tblStatisticDate INNER JOIN tblStatistics ON tblStatisticDate.StatisticDateID = tblStatistics.StatisticDateID

UNION SELECT "NumberSeatsAvailableBarMeal" AS Category, StatisticDate AS MeasurementDate, NumberSeatsAvailableBarMeal AS Measurement
FROM tblStatisticDate INNER JOIN tblStatistics ON tblStatisticDate.StatisticDateID = tblStatistics.StatisticDateID

UNION SELECT "NumberRoomsAvailableMonth" AS Category, StatisticDate AS MeasurementDate, NumberRoomsAvailableMonth AS Measurement
FROM tblStatisticDate INNER JOIN tblStatistics ON tblStatisticDate.StatisticDateID = tblStatistics.StatisticDateID;

And then I created the CROSSTAB QUERY based on the above UNION QUERY:

TRANSFORM First(BasicStatistics01.Measurement) AS FirstOfMeasurement
SELECT BasicStatistics01.Category
FROM BasicStatistics01
WHERE (((Format([MeasurementDate],"yyyy"))=2010))
GROUP BY BasicStatistics01.Category
ORDER BY Format([MeasurementDate],"mmmm yyyy")
PIVOT Format([MeasurementDate],"mmmm yyyy");

It worked great!

Regarding the solution, there is one minor issue I am trying to overcome with the CROSSTAB QUERY:

TRANSFORM First(BasicStatistics01.Measurement) AS FirstOfMeasurement
SELECT BasicStatistics01.Category
FROM BasicStatistics01
WHERE (((Format([MeasurementDate],"yyyy"))=2010))
GROUP BY BasicStatistics01.Category
ORDER BY Format([MeasurementDate],"mmmm yyyy")
PIVOT Format([MeasurementDate],"mmmm yyyy");

Whenever I change the year, for example, from 2010 to 2009, the report adjusts to show me the months for that specific year. But if I change that line to:

WHERE (((Format([MeasurementDate],"yyyy"))=[Forms]![frmSelectYear]![SelectYear]))

so that it would be equal to the year inputted in a separate form, I get the following error:

"The Microsoft Office Access database engine does not recognise '[Forms]![frmSelectYear]![SelectYear]' as a valid field name or expression."

The field in the separate form is unbound and is configured as such:

SELECT DISTINCT Format([StatisticDate],"yyyy") AS SelectYear
FROM tblStatisticDate
ORDER BY Format([StatisticDate],"yyyy") DESC;

What am I doing wrong?

I just figured out the solution regarding the CrossTab Query!

I created a Public Function GETYEAR:

Public Function GetYear(FormName As String, FieldName As String)
GetYear = Forms(FormName).Controls(FieldName)
End Function

And then modified the line in the crosstab query to:

WHERE (((Format([MeasurementDate],"yyyy"))=GetYear("frmSelectYear","SelectYear") ))

And it worked! Now just to figure out how to sort the months on the top in the correct order.