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 VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 Display Modes
  #1 (permalink)  
Old August 23rd, 2010, 05:43 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default Problem Creating Monthly Comparison Report

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!
Reply With Quote
  #2 (permalink)  
Old August 25th, 2010, 07:24 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #3 (permalink)  
Old August 25th, 2010, 07:35 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #4 (permalink)  
Old August 25th, 2010, 08:55 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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


Thread Tools
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
Report Monthly Comparison Odeh Naber Access 3 August 25th, 2010 08:55 AM
Error in Monthly Calendar Report (Ch9) mruschetti BOOK: Expert Access 2007 Programming ISBN 978-0-470-17402-9 6 May 8th, 2008 12:43 AM
apostrophe ' problem in comparison ramniwas Visual Basic 2005 Basics 4 April 4th, 2008 12:21 AM
Creating report problem Vince_421 Access VBA 8 February 26th, 2007 05:33 PM
Desc sorted monthly report across several year Fehrer Access 11 February 7th, 2004 01:26 PM



All times are GMT -4. The time now is 04:49 PM.


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