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

32ozWaterBottle

New member
Local time
Today, 14:36
Joined
Jan 28, 2020
Messages
3
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:
Code:
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

Well-known member
Local time
Today, 17:36
Joined
Apr 9, 2015
Messages
3,831
Cant you just do this?
 

Attachments

  • street qry.png
    street qry.png
    14.3 KB · Views: 28

32ozWaterBottle

New member
Local time
Today, 14:36
Joined
Jan 28, 2020
Messages
3
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

AWF VIP
Local time
Today, 13:36
Joined
Mar 9, 2014
Messages
3,424
There are not two tables - it is the same table used twice in one query.
 

Ranman256

Well-known member
Local time
Today, 17:36
Joined
Apr 9, 2015
Messages
3,831
yes, drag the table into the query twice, and each pulls it link.
 

Users who are viewing this thread

Top Bottom