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]

Re: Query writing help

Jamie wrote:[color=blue]
> 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[/color]

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]

Re: Query writing help

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 <oil@vinegar.co m> wrote in message news:<Zy3Cc.106 64$w07.1714@new sread2.news.pas .earthlink.net> ...[color=blue]
> Jamie wrote:[color=green]
> > 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[/color]
>
> 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.[/color]