Thanks Helen, I got another set of eyes to have a look at it and he
has modifies it and it's OK on both now. Thank you though for taking
the time to respond.

FYI though:-

This was fast on the small GDB and slow on the larger:

select Sales.TXRef, Shows.PlanKey, Plans.Area, Plans.Zone, Plans.Seat,
Sales.Amount, Sales.SaleType, Sales.DiscFeeID, Bookings.Comment,
Plans.Band,
Payments.Number, Payments.StartDate, Payments.Expiry,
Payments.IssueNumber
from Sales
Inner Join Bookings on (Bookings.TXRef = Sales.TXRef)
Inner Join Shows On (Sales.SeatKey = Shows.SeatKey)
Inner Join Plans on (Plans.PlanKey = Shows.Plankey)
Left Outer Join Payments on (Payments.TXRef = Sales.TXRef)
where
Sales.TXref = :TXREF
and
Shows.Code = :CODE

And this one was vice versa:-

select Sales.TXRef, Shows.PlanKey, Plans.Area, Plans.Zone, Plans.Seat,
Sales.Amount, Sales.SaleType, Sales.DiscFeeID, Bookings.Comment,
Plans.Band,
Payments.Number, Payments.StartDate, Payments.Expiry,
Payments.IssueNumber
from Sales
Inner Join Bookings on (Bookings.TXRef = Sales.TXRef)
Inner Join Shows On (Sales.SeatKey = Shows.SeatKey)
Inner Join Plans on (Plans.PlanKey = Shows.Plankey)
Left Outer Join Payments on (Payments.TXRef = Sales.TXRef)
where
Bookings.TXref = :TXREF
and
Shows.Code = :CODE

And below is the one that's OK on both:-

select Sales.TXRef, Shows.PlanKey, Plans.Area, Plans.Zone, Plans.Seat,
Sales.Amount, Sales.SaleType, Sales.DiscFeeID, Bookings.Comment,
Plans.Band,
Payments.Number, Payments.StartDate, Payments.Expiry,
Payments.IssueNumber
from Sales
inner Join Bookings on (Bookings.TXRef = Sales.TXRef)
and (sales.TXREF=:TXREF)
Inner Join Shows On (Sales.SeatKey = Shows.SeatKey)
Inner Join Plans on (Plans.PlanKey = Shows.Plankey)
Left Outer Join Payments on (Payments.TXRef = Sales.TXRef)
where
Shows.Code = :CODE