MS Access SQL sub Query using outer query result

Question

I'm attempting to run a sub query based on the result of an outer query. The issue that I am having is that instead of using the outer query, I get a prompt for a value from the subquery.

SELECT Facilities.CustomerName, Facilities.FacilityName,
   Facilities.AnnualPlan, Facilities.AppCo1, 
   (SELECT YeildDB.CornYield
    FROM YeildDB
    WHERE Facilites.AppCo1 = YeildDB.FIPS) AS Expr1
FROM Facilities

The goal is that the sub query should use the value from Facilities.AppCo1 to match with the value in YeildDB.FIPS and then return the corresponding value in YeildDB.CornYeild.

Currently I get a prompt asking for the YeildDB.FIPS value instead of the sub query using the outer query value.

Then Access is unable to locate an attribute named 'FIPS' in that table 'YeilDB''. Check the table definition. It's unlikely, but perhaps it could help writing WHERE YeildDB.FIPS = Facilites.AppCo1 instead, and add that lacking third 'i' in 'Facilites'. — Erwin Smout
– Erwin Smout, Commented Apr 11, 2019 at 10:41

Gordon Linoff · Accepted Answer · 2019-04-11 00:13:07Z

2

Your code should work. But you can also express this using a LEFT JOIN:

SELECT Facilities.CustomerName, Facilities.FacilityName,
       Facilities.AnnualPlan, Facilities.AppCo1, 
       YeildDB.CornYield
FROM Facilities LEFT JOIN
     YeildDB
     ON Facilties.AppCo1 = YeildDB.FIPS;

I noticed that you misspelled Facilities -- and that is probably why your version doesn't work. This is one reason to use table aliases:

SELECT f.CustomerName, f.FacilityName,
       f.AnnualPlan, f.AppCo1, 
       y.CornYield
FROM Facilities as f LEFT JOIN
     YeildDB as y
     ON f.AppCo1 = y.FIPS;

answered Apr 11, 2019 at 0:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Seth_ia Over a year ago

That works! Thank you so much...I must have looked at that for an hour and missed the obvious!

Programnik · Accepted Answer · 2019-04-11 00:39:23Z

0

Your sub query may be returning multiple values, hence the prompt asking you to specify which one you want. You can fix this (or at least hide this issue) by specifying top 1:

SELECT Facilities.CustomerName, Facilities.FacilityName,
   Facilities.AnnualPlan, Facilities.AppCo1, 
   (SELECT TOP 1 YeildDB.CornYield
    FROM YeildDB
    WHERE Facilites.AppCo1 = YeildDB.FIPS) AS Expr1
FROM Facilities

answered Apr 11, 2019 at 0:39

Programnik

1,5491 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

MS Access SQL sub Query using outer query result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related