Joining two fields from one table to one field in another

  • Thread starter Thread starter Heydan
  • Start date Start date
H

Heydan

I have a table that contains mileage logs. The Mileage table looks like
this:
ItemNumber (autonumber)
ItemDate (a date)
From (a location id - long integer)
To (a location id - long integer)

The second table contains locations:
LocationID (long integer)
LocationName (string)

I need to show the from and to locations for each trip. The following query
returns the error "Join expression not supported".

SELECT Mileage.ItemDate, Locations.LocationName AS [From],
Locations.LocationName AS [To]
FROM (Mileage INNER JOIN Locations ON Mileage.[From] =
Locations.LocationID) INNER JOIN Locations ON Mileage.[To] =
Locations.LocationID

I can't see anything wrong with this statement. Anyone have any ideas?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to alias the 2nd instance of Locations. E.g.:

SELECT Mileage.ItemDate, Locations.LocationName AS [From],
L2.LocationName AS [To]
FROM (Mileage INNER JOIN Locations ON Mileage.[From] =
Locations.LocationID) INNER JOIN Locations As L2 ON Mileage.[To] =
L2.LocationID

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCyGZ4echKqOuFEgEQLykQCguXHsy0OVYOg4ehOVXdo3bsXG/xYAoLZm
6d1xR66AVyJSRyP36pUdhhRQ
=XdVJ
-----END PGP SIGNATURE-----
 
Try...

SELECT Milage.ItemDate, Locations.LocationName AS [From]
Locations_1.LocationName AS [To]
FROM (Milage INNER JOIN Locations ON Milage.From
Locations.LocationID) INNER JOIN Locations AS Locations_1 ON Milage.T
= Locations_1.LocationID;



HT
 
You need 2 copies of the Table Locations. The SQL String should be
something like:

SELECT Mileage.ItemDate,
FromLocs.LocationName AS [From],
ToLocs.LocationName AS [To]

FROM
( Mileage INNER JOIN Locations AS FromLocs
ON Mileage.[From] = FromLocs.LocationID
)
INNER JOIN Locations AS ToLocs
ON Mileage.[To] = ToLocs.LocationID
 
Thanks, that was dead-on right. I never would have guessed. Thanks again.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to alias the 2nd instance of Locations. E.g.:

SELECT Mileage.ItemDate, Locations.LocationName AS [From],
L2.LocationName AS [To]
FROM (Mileage INNER JOIN Locations ON Mileage.[From] =
Locations.LocationID) INNER JOIN Locations As L2 ON Mileage.[To] =
L2.LocationID

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCyGZ4echKqOuFEgEQLykQCguXHsy0OVYOg4ehOVXdo3bsXG/xYAoLZm
6d1xR66AVyJSRyP36pUdhhRQ
=XdVJ
-----END PGP SIGNATURE-----

I have a table that contains mileage logs. The Mileage table looks like
this:
ItemNumber (autonumber)
ItemDate (a date)
From (a location id - long integer)
To (a location id - long integer)

The second table contains locations:
LocationID (long integer)
LocationName (string)

I need to show the from and to locations for each trip. The following query
returns the error "Join expression not supported".

SELECT Mileage.ItemDate, Locations.LocationName AS [From],
Locations.LocationName AS [To]
FROM (Mileage INNER JOIN Locations ON Mileage.[From] =
Locations.LocationID) INNER JOIN Locations ON Mileage.[To] =
Locations.LocationID

I can't see anything wrong with this statement. Anyone have any ideas?
Click to expand...
Click to expand...
 
Back
Top