Group by year, then by total

[jhicsupt]

I want to have a report that shows the orders in the database that have an
order date <2007. I need the report to group by order date <2007, the
highest $ amount first. However I need the report to keep the customers
together. In other words, if ABC Company has total orders in 2006 for
$10,000 and then has other orders in 2007 for $4,000, then the report would
look like:

ABC Company
2006 [Total$]=$10,000
ABC 1 radio Order date 12/1/2006 $5,000
ABC 1 radio Order date 1/1/2006 $5,000

2007 [Total$]=$5,000
ABC 1 DVD Order date 1/1/2007 $5,000

2003 [Total$]=$2,000
ABC 1 ipod Order date 1/1/2003 $2,000

XYZ Company
2006 [Total$]=$8,000
XYZ 1 radio Order date 12/1/2006 $4,000
XYZ 1 DVD Order date 12/1/2006 $4,000

and so on

Hopefully I'm giving you enough information. Thanks so much in advance.





The field names are:

Customer
Order Year

 

[Allen Browne]

In report design view, use the Sorting And Grouping dialog to group by the
company and then an expression that indicates if the date is in 2007 or not.

The first row of the dialog will contain the company name.
The second row will have an expression such as this:
=([OrderDate] >= #1/01/2007#)

You can create the group headers to give the desired appearance.
Choose Descending order on the expression to get the other years first.

If you want 2008 grouped in with the previous years (so only 2007 stands
alone), use:
=([EnteredOn]>=#1/01/2007#) And ([EnteredOn]<#1/01/2008#)