RE: Help on sorting in reports

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dear Steve
Unfortunately the solution proposed below does not bring the desired
results, because if sorting by half-twin rate only, the validity dates would
not be sorted in ascending order any longer).

My report currently is grouped / sorted as follows:
Roomtype - group header yes / group footer no / sort order ascending
Validity from - group header / footer set to no / sort order ascending
Half twin - group header / footer set to no / sort order ascending
With above setting the report outputs as shown in the first paragraph below.

Our customers however want the report to display the room-type with the
LOWEST price first, then next higher room category and so forth.
Lowest price would be evaluated by the lowest half-twin rate in the first
validity period (in below samples valid from 01-Nov-06).
The desired output / sorting is shown at the bottom of my enquiry:
1. Deluxe room (half-twin valid from 01-Nov-06 = 2.714)
2. Deluxe River view room (half-twin from 01-Nov-06 = 3.068)
3. Junior Suite (half-twin from 01-Nov-06 = 3.540)

The main problem here is that if I set grouping order for room-type, I also
have to select a sorting order (ascending or descending). I don't want the
room-types sorted in ascending or descending order though. While the
room-types should be grouped, their sorting order should be determined by the
LOWEST half-twin rate in the FIRST validty period.
The sorting order for validity periods should remain in ascending order
within each room-type (see desired sorting results at the bottom).

Thanks! Niki

"SteveS" wrote:

Hi Niki,

In reports, you need to use the "Sorting and Grouping" menu option (under
VIEW).
You will see it when you are in the report design view.

To group by room, you will need to select the field in the
"Field/Expression" column, then at the bottom of the dialog box, for the
GROUP HEADER option, select YES.

Then add the "half-twin rate" field. Leave the "Group Header" set to NO.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Niki" wrote:

I have a report for a hotel rate database, which currently sorts by "room
type" and by "valid from fields" and looks like this:
Room type Valid from Valid to SGL Half Twin 3rd pax
Deluxe River view 01-Nov-06 26-Dec-06 5,900.00 3,068.00 1534
27-Dec-06 03-Jan-07 6,608.00 3,422.00 1534
04-Jan-07 31-Mar-07 5,900.00 3,068.00 1534
01-Apr-07 31-Oct-07 4,956.00 2,596.00 1534
Deluxe room 01-Nov-06 26-Dec-06 5,192.00 2,714.00 1534
27-Dec-06 03-Jan-07 5,900.00 3,068.00 1534
04-Jan-07 31-Mar-07 5,192.00 2,714.00 1534
01-Apr-07 31-Oct-07 4,248.00 2,242.00 1534
Junior Suite 01-Nov-06 26-Dec-06 6,844.00 3,540.00 1534
27-Dec-06 03-Jan-07 7,552.00 3,894.00 1534
04-Jan-07 31-Mar-07 6,844.00 3,540.00 1534
01-Apr-07 31-Oct-07 5,900.00 3,068.00 1534
However I want the report to display the room type with the lowest room rate
first, then the room type with the next highest room rate next and so on
(lowest rate rate taken based on twin room starting on Nov 01) and the result
should loom like this:
Room type Valid from Valid to SGL Half Twin 3rd pax
Deluxe room 01-Nov-06 26-Dec-06 5,192.00 2,714.00 1534
27-Dec-06 03-Jan-07 5,900.00 3,068.00 1534
04-Jan-07 31-Mar-07 5,192.00 2,714.00 1534
01-Apr-07 31-Oct-07 4,248.00 2,242.00 1534
Deluxe River view 01-Nov-06 26-Dec-06 5,900.00 3,068.00 1534
27-Dec-06 03-Jan-07 6,608.00 3,422.00 1534
04-Jan-07 31-Mar-07 5,900.00 3,068.00 1534
01-Apr-07 31-Oct-07 4,956.00 2,596.00 1534
Junior Suite 01-Nov-06 26-Dec-06 6,844.00 3,540.00 1534
27-Dec-06 03-Jan-07 7,552.00 3,894.00 1534
04-Jan-07 31-Mar-07 6,844.00 3,540.00 1534
01-Apr-07 31-Oct-07 5,900.00 3,068.00 1534
How do I have to construct a query or the report that it groups the room
types as displayed above AND sorts them based on the lowerst half-twin rate
for the first validity period.
Thanks! Niki

.

Relevant Pages

  • RE: Help on sorting in reports
    ... I was able to figure out a way to get the report to be like your example. ... RoomTypePrntOrder - group header yes / group footer no / sort order ascending ... Lowest price would be evaluated by the lowest half-twin rate in the first ...
    (microsoft.public.access.forms)
  • Re: Sort by Date
    ... Ascending then =Month, Ascending and it gave me what I ... My report is built from a query that was set up to show the sum of each ... You need to sort a report in the report's Sorting and Grouping dialog. ...
    (microsoft.public.access.reports)
  • Re: Sorting & Grouping Problem
    ... might be using in a query, the report itself needs the Sorting/Grouping set. ... My report shows Temp, Identifier, and Data in that order from ... Order, Ascending ... In the sorting and Grouping box, if I put Order first, in ascending order, ...
    (microsoft.public.access.reports)
  • Re: Sort by date in group is not working
    ... This is a basic idea of what the report looks like at the moment. ... As you can see the weeks are in ascending order but the days are descending. ... The Sorting and Grouping options are as follows: ...
    (microsoft.public.access.reports)
  • Re: Sorting Help
    ... ascending or descending. ... Report, that is in error. ... Sorting in Reports is determined by the Sorting and Grouping property. ...
    (comp.databases.ms-access)