SubQuery Syntax

[LonnieJohnson]

I have a field that is defined by a subquery. In the subquery I am using the Where statement. In my where statement I am referencing a field in my main query.

Subquery:
(Select Total from MainQueryName Where ID = "I'm lost&quot

After ID equal is where I get lost trying to reference a field in the main query.

I hope I am making myself clear. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[DoogieB]

I'm not the greatest SQL writer, but I think that's an illegal circular reference. Your query design environment might help you to do things more graphically, then, once that's working right, goto View|SQL to see the code and learn the syntax a little better. Try making a new query that gets your "base" view, then make another query with the first qry as it's data source. It's a simple way to nest select statements.

 

[cmmrfrds]

Here is some general syntax to reference fields in the inner and outer query - use alias on the table name.

SELECT X.segmentCode, X.customerPO, X.customerID,
(SELECT top 1 Y.segmentName
FROM Detail850PO1 AS Y
where Y.seqmentName = X.segmentName ) AS Expr1
FROM Detail850PO1 AS X;