Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 31st, 2011, 03:54 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default Current Month and Year-To-Date

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!
Reply With Quote
  #2 (permalink)  
Old May 31st, 2011, 11:05 AM
Friend of Wrox
Points: 1,015, Level: 12
Points: 1,015, Level: 12 Points: 1,015, Level: 12 Points: 1,015, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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
Reply With Quote
The Following User Says Thank You to HiTechCoach For This Useful Post:
Odeh Naber (May 31st, 2011)
  #3 (permalink)  
Old May 31st, 2011, 11:24 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #4 (permalink)  
Old May 31st, 2011, 12:02 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!!!
Reply With Quote
  #5 (permalink)  
Old May 31st, 2011, 12:50 PM
Friend of Wrox
Points: 1,015, Level: 12
Points: 1,015, Level: 12 Points: 1,015, Level: 12 Points: 1,015, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Did you create the parameter in the query?
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
Reply With Quote
  #6 (permalink)  
Old May 31st, 2011, 03:28 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old May 31st, 2011, 08:41 PM
Friend of Wrox
Points: 1,015, Level: 12
Points: 1,015, Level: 12 Points: 1,015, Level: 12 Points: 1,015, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old June 1st, 2011, 03:49 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #9 (permalink)  
Old June 1st, 2011, 10:44 AM
Friend of Wrox
Points: 1,015, Level: 12
Points: 1,015, Level: 12 Points: 1,015, Level: 12 Points: 1,015, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using month & year variable to get date in range rsearing ASP.NET 2.0 Professional 2 July 5th, 2008 08:30 AM
How to pull Month & Year from Date(dd/mm/yyyy) istcomnet Classic ASP Basics 2 April 22nd, 2008 06:22 PM
Month and Year to Date on a Report Mitch Access 2 February 20th, 2007 10:23 AM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM
Date= starting of the current fiscal year rmanu7382 Reporting Services 0 April 3rd, 2006 12:42 AM



All times are GMT -4. The time now is 02:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.