Inner Join Problem with Select Statement 1

[MontgomeryPete]

Inner Join Problems with Select Statement

Hi Folks:

We are working on a scheduling system in class for a non profit agency.

There are three tables: Passengers [PassengerNameLast],[PassengerAddress]…
Locations: [LocationName], [LocationAddress]…s information)
Ride: [Date],[ApptTime],[PassengterNo],[LocationNo],...

To display the information for dispatching purposes and to produce a record of the ride, the following select statement was written that returns the passenger’s name, the pick up location and the drop location. There are other fields to select, but I have omitted them for clarity.

SELECT Ride.Date, Ride.PassengerNo, Passengers.PassengerNameLast, Ride.Time, Ride.PickUpLocation, Ride.DropOffLocation, IIf([PickUpLocation]=5610,Passengers!AddressStreet,Locations!LocationName) AS PickUp, IIf([DropOffLocation]=5610,[AddressStreet],[LocationName]) AS DropOff
FROM Locations INNER JOIN (Passengers INNER JOIN Ride ON Passengers.PassengerNo=Ride.PassengerNo) ON Locations.LocationNumber=Ride.DropOffLocation;

The value 5610 is stored in the Location Table with the LocationName field set to “Home.” If the pickup location (in the location table) is 5610, then an “if statement” should substitute the passengers address for the value “Home.” However, the select statement returns 5610 in the alias [PickUp] when the pick up location is the passenger’s home. For the alias [DropOff] the proper actual address is shown if 5610 is selected.

Thanks for any help you can give us.

Pete Davis

 

[PHV]

You have to deal with 2 different instances of the Locations table: one joined on DropOffLocation, the other joined on PickUpLocation

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MontgomeryPete]

Thanks for the prompt response. The simplest answer worked best.

PD