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!

 

[gemma-the-husky]

In general

have a master query, selecting the properties in which you are interested (Q1)

have a query (totals query) based on the current month (Q2)

have another totals query based no the range of months (1-current month) (Q3)

Take the first query, and left join it to the others, as you need the details even if they are zero.

Variations on this idea should do it.


I think a cross-tab query should give you a spread of all the months.

 

[DSnipeFunk]

Gemma-The-Husky! Thank you!

I am working on this right now - but just a related question - how would you advise I create and use the Month/Date fields?

In Table One - should I create it as a 'Date Ending' field so that for the month of January 2011 the user inserts 31.01.2011 ? And then on the form to select the month and year for the query to be based upon to use the Month(DateEnding) and Year(DateEnding) for the combo selection?

I am trying this with no success - if I leave the format parameter blank, it shows:

1 (instead of January)
2 (instead of February)
3 (instead of March)
4 (instead of April)

When I change the Format property to mmmm it shows the incorrect months:

December (instead of 1)
January (instead of 2)
January (instead of 3)
January (instead of 4)

Not sure where I am giong wrong. Any advice? Thank you in advance!

 

[DSnipeFunk]

Just an update to understand the issue with the Month/Date fields:

Assuming that I have a Short-Date field in the table that stores the Month-Ending date (i.e. 31.01.2011): I have created a form which has a combo that points to the existing Month-Ending dates in the table that I mentioned above so that the user can eventually select the month for which he wants the reports generated.

In the query underlying the combo (for selecting the month) I created three columns to demonstrate the problem. The first shows the dates as originally input. The second shows the correct 'number' value of the month. The third shows the incorrect text value for the month:

Column One: MonthEnding
Column Two: Month([MonthEnding])
Column Three: Format(Month([MonthEnding]);"mmmm")

31-01-2011 1 December
31-01-2010 1 December
31-01-2009 1 December
31-01-2008 1 December
28-02-2011 2 January
28-02-2010 2 January
28-02-2009 2 January
29-02-2008 2 January
31-03-2011 3 January
31-03-2010 3 January
31-03-2009 3 January
31-03-2008 3 January
30-04-2011 4 January
30-04-2010 4 January
30-04-2009 4 January
30-04-2008 4 January

 

[JANR]

Column Three: Format(Month([MonthEnding]);"mmmm")

use:

Column Three: Format([monthEnding];"mmmm")

JR

 

[gemma-the-husky]

I think it should be a comma - not sure what the semi colon would do.

Format(Month([MonthEnding]),"mmmm")


I would store the full date. You may end up with a query where you don't want a fixed month. If you have the true date, it's still easy.

between startdate and enddate

 

[DSnipeFunk]

JANR thank you! It worked! I have 'grouped' the field so it only shows one time each month. Now to experiment with the rest of the queries.

 

[DSnipeFunk]

Gemma-The-Husky! Thank you once again! It is really strange all the info I get from the net shows to use commas, but in my Access commas are replaced by ';' symbol. Not sure why? JANRs recommendation worked perfectly to solve the Month problem. Now I will experiment this with the rest of the advice that you provided. Will let you know how it goes! Cheers!

 

[JANR]

Access commas are replaced by ';' symbol.

I assume that you are on an europeean version of Access, the same as mine and when you use expression builder WE are forced to use ; to seperate elements in in-built functions.

Blame regionalsettings

JR

 

[Galaxiom]

The regional settings can be a nightmare especially with Format.

A while back we had a Greek user who not only had to use semicolons instead of commas but also required a different character to get month.

Every time they used an "m", Access would automatically add a literal escape (\) before it just as it does when we enter any alpha character except y, m, d, h, n, or s in the Format property of a control.

We eventually realised they had to use the local character, presumably the letter mu as the Greek word for month is μήνας

It does show how a database may not be as international as one would assume, despite best intentions. However I expect this problem could be avoided by compiling in the authoring region.

 

[DSnipeFunk]

Thanks to all for your comments!

I tackled the first two parts of the problem - I created a main query which basically lists all the data that I needed to access.

And then I created 4 queries that use the main query to give me the statistics for (1) CurrentMonth, (2) CurrentMonthLastYear, (3) CurrentYearToDate and (4) LastYearToDate.

Now only to tackle the cross-tab to get the figures for each month - Jan, Feb, Mar, Apr, etc. - across the top of the page.

Cheers all!

 

[DSnipeFunk]

I am sorry folks but I am having a difficult time with the crosstab. 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 have the months listed on the top.

Thank you!

 

[DSnipeFunk]

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!!!