>Hi Fabio
>
>Thanks for the code. Maybe I missed something but I am not able to get the result. I am getting all zeros in the iPartyID column. How can I make the subquery only look for the current tDocs.iID of the main query? Without the second table in the FROM of the subquery I am getting the error that such a subquery is not allowed.
>
>Please advise.
>
>

SELECT tDocs.*, ;
>		mLookup.cValue AS cLevel1, ;
>		mLookup_A.cValue AS cLevel2, ;
>		mLookup_B.cValue AS cLevel3, ;
>		(SELECT TOP 1 SDP.iPartyID ;
>			FROM VSO3!sDocsParty SDP, VSO3!tDocs TD ;
>			WHERE SDP.iPID = TD.iID ;
>			ORDER BY SDP.iID) AS iPartyID ;
>	FROM VSO3!tDocs ;
>		INNER JOIN VSO3!mLookup ;
>				ON tDocs.ILvl1ID = mLookup.iID ;
>		INNER JOIN VSO3!mLookup mLookup_A ;
>				ON tDocs.ILvl2ID = mLookup_A.iID ;
>		INNER JOIN VSO3!mLookup mLookup_B ;
>				ON tDocs.ILvl3ID = mLookup_B.iID ;
>	ORDER BY mLookup.cValue, mLookup_A.cValue, mLookup_B.cValue

>

SELECT  TD.*, ;
		mLookup.cValue AS cLevel1, ;
		mLookup_A.cValue AS cLevel2, ;
		mLookup_B.cValue AS cLevel3, ;
		(SELECT TOP 1 iPartyID ;
			FROM VSO3!sDocsParty SDP ;
			WHERE iPID = TD.iID ;
			ORDER BY iID) AS iPartyID ;
	FROM VSO3!tDocs  TD ;
		LEFT JOIN VSO3!mLookup ;
				ON TD.ILvl1ID = mLookup.iID ;
		LEFT JOIN VSO3!mLookup mLookup_A ;
				ON TD.ILvl2ID = mLookup_A.iID ;
		LEFT JOIN VSO3!mLookup mLookup_B ;
				ON TD.ILvl3ID = mLookup_B.iID ;
	ORDER BY mLookup.cValue, mLookup_A.cValue, mLookup_B.cValue

>The following query, which will be my aim, complains that iPartyID is not found.
>

SELECT tDocs.*, ;
>		mLookup.cValue AS cLevel1, ;
>		mLookup_A.cValue AS cLevel2, ;
>		mLookup_B.cValue AS cLevel3, ;
>		(SELECT TOP 1 SDP.iPartyID ;
>			FROM VSO3!sDocsParty SDP, VSO3!tDocs TD ;
>			WHERE SDP.iPID = TD.iID ;
>			ORDER BY SDP.iID) AS iPartyID, ;
>		mParty.cName, ;
>		mParty.cCity ;
>	FROM VSO3!tDocs ;
>		INNER JOIN VSO3!mLookup ;
>				ON tDocs.ILvl1ID = mLookup.iID ;
>		INNER JOIN VSO3!mLookup mLookup_A ;
>				ON tDocs.ILvl2ID = mLookup_A.iID ;
>		INNER JOIN VSO3!mLookup mLookup_B ;
>				ON tDocs.ILvl3ID = mLookup_B.iID ;
>		INNER JOIN VSO3!mParty mParty ;
>				ON iPartyID = mParty.iID ;
>	ORDER BY mLookup.cValue, mLookup_A.cValue, mLookup_B.cValue