Help on sorting in reports

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Niki,

I was able to figure out a way to get the report to be like your example.

I don't know what tables you have in your MDB, so I made another table,
"tblRoomTypes", with fields:

"ID" (autonumber/PK),
"RoomType" (text) - text description of the room
& "RoomTypePrntOrder" (number/byte) - order to print groups

(If you already have a table for room types, you could use it, adding a
field for print order.)

Since I knew the order, I manually entered 1, 2, 3 in the table for the
order I wanted the rooms to be printed.

Then I modified the report recordsource to:

SELECT tblRates.Room_type, tblRates.Valid_from, tblRates.Valid_to,
tblRates.SGL, tblRates.Half_Twin, tblRates.[3rd_pax],
tblRoomTypes.RoomTypePrntOrder
FROM tblRates LEFT JOIN tblRoomTypes ON tblRates.Room_type =
tblRoomTypes.RoomType;


Sorting and Grouping is set up like this:

RoomTypePrntOrder - group header yes / group footer no / sort order ascending
Valid_from - group header no / group footer no / sort order
ascending
Half_Twin - group header no / group footer no / sort order
ascending

In the [RoomTypePrntOrder] group header is [Room_Type]. All the other fields
are in the detail section.


So now its just a matter of writing code to enter the print order
(RoomTypePrntOrder) before the report is opened/printed.


HTH

 

[Guest]

Hi Steve,
many thanks for your suggestion - I was thinking about adding sequence
numbers manually as well, but hoped there would be a more "elegant" solution
to get the room-types grouped and printed according to their lowest price in
the first validity season. Asking user to add a "sort order" manually after
they input the various room types and prices for each validity period is
prone to possible errors, plus we have over a thousand hotels in the database
(and each hotel has numerous room-types). If there is no way to
programtically determin the lowest price for each hotel's room-type id in the
first validity period and then sort accordingly, then we'll have to go with
your proposed solution by adding 1 field in which we enter the print order
manually.

Thanks again and kind regards!
Niki

 

[Guest]

So you have a table of room types? What is its structure? How many different
room types?

In the rate tabel, are you storing the text desc of the room type ("Deluxe
Room") or the PK of the room type table?

It can be automated using code. And possibly by using SQL. I don't know how
to begin to do the sort in SQL, but the code shouldn't be too hard.

 

[Guest]

Niki,

OK, So I decided to try and automate setting the sort order. I came up with
two ways.


The first is with an extra table (or two depending on your tables) and about
100 lines of code. This might not work too well in a multiuser enviroment.

The second way ( a more "elegant" solution ) is with SQL and a function
written by Stephen Lebans named "Serialize()".

I made a totals query (subQuery1) based on the table tblRates. Then I made
another query (Query1) based on the subQuery1, adding the Seralize()
function. The field looks like:

intRoomTypePrntOrder: Serialize("subQuery1","strRoom_type",[strRoom_type])

This function numbers the rows of the query "on-the-fly".


Then I made a third query, which will be the recordsource for the report,
using tblRates and Right Joined with Query1.

Set the sorting and grouping as in the previopus post>

If you email me, I will send you both examples.

HTH