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
| FAQ | Members List | 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 February 2nd, 2004, 08:13 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Desc sorted monthly report across several year

Hi guys,

I had a problem in displaying monthly product sales volume in a report by descending, which is generated from the query. In that query, I tried to use both the month name column using this expression Month Name:Format$([OrderDate], "mmmm yyyy,0,0") and month number column with this expression Month Number:DatePart("m",[OrderDate]).
Even after I set the sorting and grouping of the month name within the report by descending , it always display the report like this....


November 2003
product A
     sales volume: 250
productB
     sales volume: 247

January 2004
product A
     Sales Volume: 300
product B
     Sales Volume: 285

February 2004
product A
     Sales Volume: 30
product B
     Sales Volume: 25

December 2003
product A
     Sales Volume: 287
product B:
     Sales Volume: 260


In fact, the proper report display that I want it to be is from the recent months goes back to the oldest month, like this

February 2004
product A
     sales volume: 30
product B
     sales volume: 25

January 2004
product A
     sales volume: 300
product B
     sales volume: 285

December 2003
product A
     sales volume:287
product B
     sales volume:260

November 2003
product A
     sales volume:250
product B
     sales volume: 247

So, how could i create the report like this? Any help and advice are greatly appreciated.Thanks.

Cheers,

Fehrer
Reply With Quote
  #2 (permalink)  
Old February 2nd, 2004, 09:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Fehrer

Your problem is here: Format$([OrderDate], "mmmm yyyy,0,0")

What this code is saying is, change the OrderDate to mmmm yyyy format and then convert to a string. When you subsequently order the column Access will do an alphabetical sort, not a date one.

A couple of things to try:
 
  • Use Format, rather than Format$.
  • Add another column to the query to hold the unformatted OrderDate. Sort on this column and group by the Month Name column



Brian Skelton
Braxis Computer Services Ltd.
Reply With Quote
  #3 (permalink)  
Old February 3rd, 2004, 08:04 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Braxis


A couple of things to try:
  • Use Format, rather than Format$.
  • Add another column to the query to hold the unformatted OrderDate. Sort on this column and group by the Month Name column


Brian Skelton
Braxis Computer Services Ltd.

Hi Brian,

Thanks for the quick response. I tried ur advice by using the format instead of format$ and adding another unformatted date columns within the query (in this case, orderDate), the query works perfectly just what i want. The date was sorted descending start from february 2004, january 2004, december 2003, and november 2003. Here is the query in SQL view:

SELECT qry_ProductSalesVolume.ProductID, qry_ProductSalesVolume.ProductName, Format([OrderDate],"mmmm yyyy") AS MonthName, qry_ProductSalesVolume.OrderID, qry_ProductSalesVolume.OrderNote, Sum(qry_ProductSalesVolume.Quantity) AS SumOfQuantity1, qry_ProductSalesVolume.OrderDate
FROM qry_ProductSalesVolume
GROUP BY qry_ProductSalesVolume.ProductID, qry_ProductSalesVolume.ProductName, Format([OrderDate],"mmmm yyyy"), qry_ProductSalesVolume.OrderID, qry_ProductSalesVolume.OrderNote, qry_ProductSalesVolume.OrderDate
ORDER BY qry_ProductSalesVolume.OrderDate DESC;

However, in the report the month name column was changed by itself as if it were string data types, like this:

january 2004
november 2003
february 2004
december 2003

So, why the result within the query was different from the one within the report? And how can I display the report just like the one within the query?

Many thanks,

Fehrer
Reply With Quote
  #4 (permalink)  
Old February 3rd, 2004, 07:09 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Cnange the format of the textbox on the report.



Sal
Reply With Quote
  #5 (permalink)  
Old February 4th, 2004, 11:07 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 Cnange the format of the textbox on the report.



Sal

Hi Sal,

Thanks for ur concern on my problem. I have changed the format of MonthName textbox within the report into =Format([OrderDate],"mmmm yyyy")...as well as setting the sorting and grouping menu at the report design view with the MonthName Header descending.

But report still display the wrong sorting order...like this

november 2003
january 2004
february 2004
december 2003

The report's displaying the correct monthly sales volume information, it's just not the view that I want to be. Was there something wrong while I design the report? I tried to create the report using both the report wizard and design view, but it's still not working...:(
Any advice would be very much appreciated.

Cheers,

Fehrer


Reply With Quote
  #6 (permalink)  
Old February 4th, 2004, 12:01 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try changing your group by clause to this:

GROUP BY qry_ProductSalesVolume.ProductID, qry_ProductSalesVolume.ProductName,
qry_ProductSalesVolume.OrderDate,
qry_ProductSalesVolume.OrderID,
qry_ProductSalesVolume.OrderNote,
Format([OrderDate],"mmmm yyyy")
ORDER BY qry_ProductSalesVolume.OrderDate DESC;


A Group By Clause will also sort as it groups. If that does not work, try removing the
Format([OrderDate],"mmmm yyyy")

from the query all toghether and do the Formt calcuation on the form/report.

Does that work?



Sal
Reply With Quote
  #7 (permalink)  
Old February 5th, 2004, 10:53 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 Try changing your group by clause to this:

GROUP BY qry_ProductSalesVolume.ProductID, qry_ProductSalesVolume.ProductName,
qry_ProductSalesVolume.OrderDate,
qry_ProductSalesVolume.OrderID,
qry_ProductSalesVolume.OrderNote,
Format([OrderDate],"mmmm yyyy")
ORDER BY qry_ProductSalesVolume.OrderDate DESC;


A Group By Clause will also sort as it groups. If that does not work, try removing the
Format([OrderDate],"mmmm yyyy")

from the query all toghether and do the Formt calcuation on the form/report.

Does that work?



Sal

Hi Sal,

Thanks for ur effort in helping me. Changing the group by clause within the query didn't work either.
When I tried ur 2nd advice by removing the Format([OrderDate],"mmmm yyyy") from the query, it still didn't work...even I couldn't sort it as descending, ...the monthly sales volume was mixed from every order date, something like this:

January 2004
product A
  Sales Volume: 300
     OrderID OrderDate Quantity
        89 01/01/04 50
        175 15/01/04 100
        62 09/12/03 150

product B
  Sales Volume: 285
     OrderID OrderDate Quantity
        120 05/01/04 100
        30 15/12/03 100
        49 20/12/03 85

December 2004
product A
  Sales Volume: 200
     OrderID OrderDate Quantity
        125 07/01/04 100
        35 17/12/03 100

product B
  Sales Volume: 100
     OrderID OrderDate Quantity
        130 09/01/04 80
        40 21/12/03 20


When I looked back at the query, the descending sorting is working ...it was sorted at the orderDate.

So I was thinking to still use Format([OrderDate], "mmmm yyyy") within the query ...and on the grouping and sorting menu at the report design view, I set the grouping base on the orderdate and productName...and at the orderDate Header I change the format of the textbox from the orderDate into Format([OrderDate], "mmmm yyyy")...but it still also didn't work either...:(

At the moment, even the report's containing the correct monthly sales volume information...users still have to click many times to find the current month sales volume (in this case, february) as the first page was the monthly sales volume at January...

Do u have any other suggestion?

Cheers,

Fehrer






Reply With Quote
  #8 (permalink)  
Old February 5th, 2004, 10:14 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you want to order tho complete recordset first by the date, put the date first on the order by clause or on the group by clause. Right now the date is on the third position on the group by clause.



Sal
Reply With Quote
  #9 (permalink)  
Old February 6th, 2004, 12:49 PM
Authorized User
 
Join Date: Aug 2003
Location: , , Australia.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 If you want to order tho complete recordset first by the date, put the date first on the order by clause or on the group by clause. Right now the date is on the third position on the group by clause.



Sal
Hi Sal,

Thanks, the report does sort the monthly sales volume in descending order. However, now the report becomes something like this:

January 2004
product A
Sales Volume: 50
     OrderID OrderDate Quantity
        89 31/01/04 20
        95 31/01/04 20
        101 31/01/04 10

January 2004
product B
Sales Volume: 60
     OrderID OrderDate Quantity
        80 30/01/04 25
        75 30/01/04 35

.
.
.

January 2004
product A
Sales Volume: 60
     OrderID OrderDate Quantity
        25 01/01/04 25
        23 01/01/04 35

January 2004
product B
  Sales Volume: 60
     OrderID OrderDate Quantity
        10 01/01/04 40
        8 01/01/04 20

December 2003
product A
Sales Volume: 200
     OrderID OrderDate Quantity
        15 31/12/03 100
        10 31/12/03 100

The order by clause or group by clause in my query is like this..

GROUP BY qry_ProductSalesVolume.OrderDate, qry_ProductSalesVolume.ProductID,
qry_ProductSalesVolume.ProductName,
qry_ProductSalesVolume.Quantity,
qry_ProductSalesVolume.OrderID,
qry_ProductSalesVolume.OrderNote,
Format([OrderDate],"mmmm yyyy")
ORDER BY qry_ProductSalesVolume.OrderDate DESC;

This sorting view in presenting the report descendingly really gives me headache. Does the order of the select statement on my query affect the view of the report?

Cheers,

Fehrer
Reply With Quote
  #10 (permalink)  
Old February 6th, 2004, 01:34 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes.

You can also change the sorting on the actual report under sorting and grouping. That will utimatly determine the sort order.



Sal
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
Error in Monthly Calendar Report (Ch9) mruschetti BOOK: Expert Access 2007 Programming ISBN 978-0-470-17402-9 6 May 7th, 2008 11:43 PM
To solve the Exception in Monthly recurring events amoghgr C# 0 August 21st, 2007 06:21 AM
Month and Year to Date on a Report Mitch Access 2 February 20th, 2007 10:23 AM
Viewing Crystal Report sorted by CustomerId ronbora Crystal Reports 0 November 18th, 2004 10:07 AM
Shows monthly records leion General .NET 1 May 14th, 2004 07:34 AM



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


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