Sorting Help!

[Robertnobok]

i need to have a report to sort and group the following way. we use Springer-Miller. i have 4 Reservations, and i would like to sort it like this: (Example)____RESERVATION INFORMATION__RESNO ARRIVAL UNIT NIGHTS REVENUE__1200AA 01/01/09 WP19 2 $200.00__1200BB 01/01/09 WH59 2 $900.00__1200CC 01/01/09 WP19 2 $200.00__1200DD 01/01/09 WP23 2 $400.00____SPECIAL BILLING ITEMS (DEPOSIT & WAIVERS)__CODE DESCRIPTION QTY AMOUNT TOTAL__DEPP19 19^ PB DEPOSIT 1 $100.00 $100.00__DEPH59 59^ HB DEPOSIT 1 $250.00 $250.00____FOLIO PAYMENT INFORMATION (TRANSACTIONS)__CODE DESCRIPTION CHARGE PAYMENT BALANCE__CADV/M CRES ADV DEPOSIT V/M $250.00 $250.00____I think you get the idea... i want to sort and group this all on one page... someone at SMS create a quick report to show me how it could be done, but as you see below, it doesn^t work, it doesn^t show the Reservation Information first, but it shows the Special Bill and Transactions currectly... and the reason is because the SCOD exists..____SORT_RES = iif(isblank(scod) and isblank(tres),resno,iif(isblank(scod) and not isblank(tres), ^999999^,iif(not isblank(scod) and isblank(tres),^999998^,^xxxxx^)))____Can you please help?!! thanks__

[kfleming]

To answer the question, I need to know more about your data structures.____Does the master table have one record for each reservation and do the special billing and transactions come from two separate tables that are linked to the master using RESNO as the common field?____Kathleen__R&R Support__

[Robertnobok]

yes, the master table is IN_RES (one record for each reservation), and the Special Billing table is a relation from IN_RES (related using RESNO), one to many... and the Transaction table is also linked from IN_RES, using GUESTNUM RESNO, one to many

[Robertnobok]

>To answer the question, I need to know more about your data __>structures. __>__>Does the master table have one record for each reservation __>and do the special billing and transactions come from two __>separate tables that are linked to the master using RESNO as __>the common field? __>__>Kathleen __>R&R Support ______yes, the master table is IN_RES (one record for each reservation), and the Special Billing table is a relation from IN_RES (related using RESNO), one to many... and the Transaction table is also linked from IN_RES, using GUESTNUM RESNO, one to many. thanks!

[Robertnobok]

>And you need a list of all of the reservations first __>followed by a list of the records from each of the scanned __>tables? __>__>Kathleen __>R&R Support ______Correct... if i have 4 reservations, i want to list (group) those first, make any subtotals. followed by all the Special Billing for all 4 reservations, subtotaling... and lastly, all the Transactions for all 4 reservations.____thank you Kathleen!!

[kfleming]

And you need a list of all of the reservations first followed by a list of the records from each of the scanned tables?____Kathleen__R&R Support

[Robertnobok]

yes, the master table is IN_RES (one record for each reservation), and the Special Billing table is a relation from IN_RES (related using RESNO), one to many... and the Transaction table is also linked from IN_RES, using GUESTNUM RESNO, one to many... thanks!

[kfleming]

There is a way to get this to work but it is relatively complex. If you can email your report to me (the .RRW file) I will see if I can come up with a design change that will get the desired result.____Kathleen__R&R Support__livesupport@livewarepub.com