Display reference value instead of ID for two fields in one table sharing same primary key in another table

[32ozWaterBottle]

Hello,

I have two tables:

tblStreet
Street ID
StreetName

and

tblCellTowerLocation
CellTowerID
OnStreetID
AtStreetID

I am trying to link StreetID in a 1-to-many relationship with OnStreetID and AtStreetID . After that, i want to do a query to display tblCellTowerLocation with the StreetName instead of the actual IDs for OnStreetID and AtStreetID. Using the Access GUI, I can only query one column (OnStreetID) to display StreetName. When I add a second StreetName, it repeats OnStreetID.

I asked a friend and they provided me a SQL query. However, Access does not allow for CTEs. The 'WITH' function does not work. Is there a way to write a SQL query around a CTE such as using a subquery for this scenario? Or am I missing something really simple? Or is my database design incorrect?

The following is the SQL query I was provided:

WITH OnStreetName
AS(
  SELECT
SL.CellTowerID,
S.StreetName AS
OnStreetName
FROM
Streets S
INNER JOIN CellTower SL
ON S.StreetID = SL.OnStreetID
),
AtStreetName
AS(
SELECT
SL.CellTowerID,
S.StreetName AS AtStreetName
FROM
Streets S
INNER JOIN CellTower SL
ON S.StreetID = SL.AtStreetID)
SELECT
SL.CellTowerID,
OSN.OnStreetName,
ASN.AtStreetName
FROM
OnStreetName OSN
INNER JOIN AtStreetName ASN
ON OSN.CellTower.ID=ASN.CellTowerID

Thanks.

 

[Ranman256]

Cant you just do this?

 

[32ozWaterBottle]

Cant you just do this?

Thank you for your response. Currently tblStreet is the master list of all street names in my service area. Wouldn't creating two duplicate street tables introduce redundancy to the database?

 

[June7]

There are not two tables - it is the same table used twice in one query.

 

[Ranman256]

yes, drag the table into the query twice, and each pulls it link.

 

[32ozWaterBottle]

Wow, it really was that simple. Thank you Ranman256 and June7!!