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

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