Record selection - Excluding rows w/ duplicate data in multiple fields

[jstoehner]

Greetings, CR gurus!

We have CR 10 connected via ODBC to an Oracle db.

I'm getting a little over my head and desperately need some help with record selection. Our database tracks travel & leisure reservations booked for our customers. I am tasked with generating a list to be run weekly showing completed reservations so we can send a satisfaction survey.

For this project, here are the pertinent fields for each reservation record:

Booking ID #
Customer ID #
Customer Name (Last, First)
Service Facility #
Reservation Date/Time
Reservation Type
Reservation ID #

When a customer calls, they might book multiple reservations (tee time + dining reservation + hotel). They might also book multiple individuals such as spouses/dependents (same Cust ID#) or even friends who have their own Cust ID#. All of these scenarios would be a separate row/record in the database, identified by a unique Reservation ID# but they are all under a single Booking ID#.

The tricky part is that we only want to send a survey to the person who booked the reservation. That person will always be the first person who appears when you retrieve all reservations, group them by Booking ID#, and then sort within the Booking by Reservation ID# in ascending order.

Here's an example of how the data might look.

BOOKING ID# CUST ID# CUST NAME FACILITY # RESV DATE RES TYPE RES ID#
10 S100 MR SMITH 90100 10/1/2006 GOLF 1234
10 S100 MS SMITH 90100 10/1/2006 GOLF 1235
10 G200 MR GRAY 90100 10/1/2006 GOLF 1236
10 S100 MR SMITH 90200 10/1/2006 DINING 1237
10 S100 MS SMITH 90200 10/1/2006 DINING 1238
10 G200 MR GRAY 90200 10/1/2006 DINING 1239
10 S100 MR SMITH 90300 10/2/2006 THEATER 1240
10 S100 MS SMITH 90300 10/2/2006 THEATER 1241
10 G200 MR GRAY 90300 10/2/2006 THEATER 1242

In this example, there are 3 individuals in the booking -- Mr & Mrs Smith (S100) and their friend Mr. Gray (G200). They made golf, dining, and theater reservations at different facilities and on different dates. Mr. Smith is the reservation maker as indicated by having the lowest Res ID#.

The desired result is to only select each of the records for MR SMITH (since he booked it) to survey their golf, dining, and theater experience. The rows for MS. SMITH and MR. GRAY would not show up in the report. The record selection must be done within each Booking ID as the same customer might call back the next day and book additional reservations on a new Booking (which we'd want to survey as well).

Any ideas?????

Thanks!

 

[synapsevampire]

Group by the Booking ID, then the RES TYPE,

Suppress the Booking group header and footer, and the RES TYPE group footer, and suppress the details and place all fields in the RES TYPE group header.

-k

 

[jstoehner]

Thank you very much for the tips. That gets me REALLY close to the desired results. The only issue I've noticed with your steps is that if the customer scheduled more than one of the same reservation types, only one is captured and the other(s) are surpressed.

I think my example was incomplete. The example I gave had this party scheduling one GOLF reservation, a DINING reservation, and a THEATER reservation. Suppose they made a 2nd GOLF reservation at a different facility within the same booking. The steps you provided would suppress the 2nd GOLF reservation and still only return 1 GOLF, 1 DINING, and 1 THEATER.

Can this be fixed?

Thanks again!!!!!!!!!!!!!!!!!!!!!

 

[Margiola]

Hi, create a FACILITY group and move this between the Booking ID and Res Type groups so it is the second group. Suppress GH2 and GF2.

 

[jstoehner]

THANK YOU, THANK YOU, THANK YOU!

I think that's going to do the trick.