Query writing help

[Jamie]

Hello,

Please can you help me.

The Booking Detail table shows each leg of a journey. BookingRef 1 is
a booking for an adult and a child to fly from London to Paris and
return. BookingRef 2 is a booking for an Adult to fly from London to
New York via Paris and then return. PassType A is an Adult ticket and
PassType C is a Child ticket.

Bookings Detail Table

BookingRef Depart Arrive PassType Revenue
1 L P A 10
1 P L A 15
1 L P C 10
1 P L C 20
2 L P A 10
2 P L A 15
2 P N A 30
2 N P A 35

Want I want to end up with is a report of the revenues earned on each
return journey (ie London to Paris = 80, Paris to New York = 65).

I thought of doing it this way but I'm sure there are alternatives.

I need a way for the query to recognise that a record with Depart = L,
Arrive = P is the same journey as a Depart = P, Arrive = L and then
combine these records into one and add the revenues. The result of the
query should look like this

BookingRef Depart Arrive PassType Revenue
1 L P A 25
1 L P C 30
2 L P A 25
2 P N A 65

I would then put the results of this query into Excel for PivotTable
analysis giving

Depart Arrive Revenue
L P 80
P N 65

[Salad]

Jamie wrote:

Hello,

Please can you help me.

The Booking Detail table shows each leg of a journey. BookingRef 1 is
a booking for an adult and a child to fly from London to Paris and
return. BookingRef 2 is a booking for an Adult to fly from London to
New York via Paris and then return. PassType A is an Adult ticket and
PassType C is a Child ticket.

Bookings Detail Table

BookingRef Depart Arrive PassType Revenue
1 L P A 10
1 P L A 15
1 L P C 10
1 P L C 20
2 L P A 10
2 P L A 15
2 P N A 30
2 N P A 35

Want I want to end up with is a report of the revenues earned on each
return journey (ie London to Paris = 80, Paris to New York = 65).

I thought of doing it this way but I'm sure there are alternatives.

I need a way for the query to recognise that a record with Depart = L,
Arrive = P is the same journey as a Depart = P, Arrive = L and then
combine these records into one and add the revenues. The result of the
query should look like this

BookingRef Depart Arrive PassType Revenue
1 L P A 25
1 L P C 30
2 L P A 25
2 P N A 65

I would then put the results of this query into Excel for PivotTable
analysis giving

Depart Arrive Revenue
L P 80
P N 65

I would consider adding another field...Leg. Then you could sequence them.

Not all round trips are point A to B B To A. Amsterdam To Paris. Paris
To Rome. Rome To Amsterdam is an example. You might have someone take
off from Paris To London then decide to take a train back or perhaps a car.

IOW, you might want to know which is the initial starting record and
which is the ending record.

[Jamie]

Thanks for the reply salad,

Here is more explaination about the problem.

If a journey goes A to B then B to C Then C to B Then B to A i want to
add together the records for A to B with B to A and B to C with C to A
giving two return legs of the journey. Im not interested in finding A
to C then C to A. If a journey only goes A to B and no return then
this should be put in a new record for A to B and then counted with
all the other A to B (and return) records.

Unfortunatly i do not have the authority to add fields to the database
so i have to work with what i have got.

Any further answers much appreciated.

Jamie

Salad <oi*@vinegar.com> wrote in message news:<Zy******************@newsread2.news.pas.eart hlink.net>...

Jamie wrote:

Hello,

Please can you help me.

The Booking Detail table shows each leg of a journey. BookingRef 1 is
a booking for an adult and a child to fly from London to Paris and
return. BookingRef 2 is a booking for an Adult to fly from London to
New York via Paris and then return. PassType A is an Adult ticket and
PassType C is a Child ticket.

Bookings Detail Table

BookingRef Depart Arrive PassType Revenue
1 L P A 10
1 P L A 15
1 L P C 10
1 P L C 20
2 L P A 10
2 P L A 15
2 P N A 30
2 N P A 35

Want I want to end up with is a report of the revenues earned on each
return journey (ie London to Paris = 80, Paris to New York = 65).

I thought of doing it this way but I'm sure there are alternatives.

I need a way for the query to recognise that a record with Depart = L,
Arrive = P is the same journey as a Depart = P, Arrive = L and then
combine these records into one and add the revenues. The result of the
query should look like this

BookingRef Depart Arrive PassType Revenue
1 L P A 25
1 L P C 30
2 L P A 25
2 P N A 65

I would then put the results of this query into Excel for PivotTable
analysis giving

Depart Arrive Revenue
L P 80
P N 65

I would consider adding another field...Leg. Then you could sequence them.

Not all round trips are point A to B B To A. Amsterdam To Paris. Paris
To Rome. Rome To Amsterdam is an example. You might have someone take
off from Paris To London then decide to take a train back or perhaps a car.

IOW, you might want to know which is the initial starting record and
which is the ending record.