MS Access: How to use calculated field in a sub query?

I have problem with the following query

Select A.PName, (B.Hours+C.Hours) as TotalHours,   
(select top 1 grade from TableD where TableD.HoursRequire >=**TotalHours**)  
from TableA A  
left join TableB B on A.Pname=B.Pname  
left join TableC C on A.Pname=C.Pname

My problem is that the calculated field "TotalHours" is not recogn开发者_C百科ized in the sub query, any help would be appreciated.

Will you get what you want by using the field expression (B.Hours+C.Hours) in the subquery instead of the alias TotalHours?

Select A.PName, (B.Hours+C.Hours) as TotalHours,
(select top 1 grade from TableD where TableD.HoursRequire >= (B.Hours+C.Hours))
from TableA A
left join TableB B on A.Pname=B.Pname
left join TableC C on A.Pname=C.Pname

However you're using left joins, and I suspect for rows where either B.Hours or C.Hours is Null, you will get nothing from the subquery because B.Hours+C.Hours will be Null and TableD.HoursRequire >= Null will never evaluate as True. The Nz() function could be useful here.

Edit: @cyberwiki is spot on Re Top 1 without ORDER BY. Sorry I missed that.

Why do you have Hours split between 2 tables (TableB and TableC)? Show us some sample data from those tables.

Edit2: If it's impractical to consolidate Hours into a single physical table, you can still do it with a "virtual" table using a Union query.

Save this as qryUnionHours:

SELECT Pname, Hours
FROM TableB
UNION ALL
SELECT Pname, Hours
FROM TableC;

Then you can use qryUnionHours as the data source for a GROUP BY query, qryHoursPerPerson, which totals the hours for each person.

SELECT Pname, Sum(Hours) AS TotalHours
FROM qryUnionHours
GROUP BY Pname;

Actually you may prefer a single query which incorporates the SQL from qryUnionHours as a subquery. I did it with 2 because I thought it might be easier to understand and less prone to syntax errors. All you need to do then is integrate your Grade assignments as we discussed in your other thread.

Use the entire expression. TOP 1 also doesn't make sense without ORDER BY unless you want a random grade.

Select A.PName, (B.Hours+C.Hours) as TotalHours,
    (select top 1 grade from TableD
     where TableD.HoursRequire >= B.Hours+C.Hours
     order by grade desc)
from TableA A
left join TableB B on A.Pname=B.Pname
left join TableC C on A.Pname=C.Pname

You can also subquery it first

SELECT PName, TotalHours,
    (select top 1 grade from TableD
     where TableD.HoursRequire >= TotalHours
     order by grade desc)
FROM (
    Select A.PName, (B.Hours+C.Hours) as TotalHours
    from TableA A
    left join TableB B on A.Pname=B.Pname
    left join TableC C on A.Pname=C.Pname
) AS SUBBED