subquery with more than one field - EXISTS reserved word 1

[sub5]

Hello all,
I am trying to cut down on queries by incorporating some subqueries into their parents. In this case Query2 was originally set-up to find unmatched records from Query1.

The code below doesn't exclude the records from Query1.

Originally I thought I would use the NOT IN clause, but I get the error message:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main queries FROM clause. Revise the SELECT statment of the subquery to request only one field."


SELECT [Refno] & " / " & [Product] AS comb, [T-REF].REFno, [Tp-Product].Product
FROM [Tp-Product] RIGHT JOIN (([T-REF] RIGHT JOIN [T-Doc] ON [T-REF].REFID = [T-Doc].refID) RIGHT JOIN [T-DocEng] ON [T-Doc].DocID = [T-DocEng].DocID) ON [Tp-Product].ProductID = [T-DocEng].productID
WHERE ((([T-DocEng].DocEngID)<>Exists (SELECT [T-DocEng].DocEngID, [Tp-ProductNoToContract].ProductRegID
FROM [T-DocEng] INNER JOIN [Tp-ProductNoToContract] ON [T-DocEng].DocEngID = [Tp-ProductNoToContract].DocEngID
WHERE ((([Tp-ProductNoToContract].ProductRegID) like [forms]![frm1]![cbo1))))
GROUP BY [T-REF].REFno, [Tp-Product].Product
ORDER BY [T-REF].REFno, [Tp-Product].Product;

How do I modify the code so that the main query EXCLUDES all the records in the subquery?

Thankyou

 

[jedraw]

Sub5,

Try this:

SELECT [Refno] & " / " & [Product] AS comb
, [T-REF].REFno, [Tp-Product].Product
FROM [Tp-Product] RIGHT JOIN (([T-REF] RIGHT JOIN [T-Doc] ON [T-REF].REFID = [T-Doc].refID) RIGHT JOIN [T-DocEng] ON [T-Doc].DocID = [T-DocEng].DocID) ON [Tp-Product].ProductID = [T-DocEng].productID
WHERE ((([T-DocEng].DocEngID)
AND NOT Exists
(SELECT "X"
FROM [T-DocEng] INNER JOIN [Tp-ProductNoToContract] ON [T-DocEng].DocEngID = [Tp-ProductNoToContract].DocEngID
WHERE ((([Tp-ProductNoToContract].ProductRegID) like [forms]![frm1]![cbo1))))
GROUP BY [T-REF].REFno, [Tp-Product].Product
ORDER BY [T-REF].REFno, [Tp-Product].Product;

 

[sub5]

Hi jedraw, thank you for your input. I have tried it but now I don't get any records instead of all of them.

 

[ddiamond]

sub5,

I can see why neither of the queries work quite right, but I am not entirely sure precisely how you want the sub-query to tie to the parent query. Can you post your original 2 queries that you are trying to combine into one query? I think that will make things much clearer.

- Dan

 

[ddiamond]

Also, can you post your original version of the query using the not in clause. You should be able to do it that way. Your error message may have been mis-leading.

 

[sub5]

Hi Dan,
The code I originally posted I simplified a little.(sorry the code I am posting here is a bit more "messy").

Here is the full version of the original independant "subquery":

SELECT [T-SideletterEng].SideletterEngID, [Tp-EngNoToContract].EngRegID
FROM [T-SideletterEng] INNER JOIN [Tp-EngNoToContract] ON [T-SideletterEng].SideletterEngID = [Tp-EngNoToContract].SideletterEngID
WHERE ((([Tp-EngNoToContract].EngRegID) Like [forms]![frm7_0EngnoRegAssign]![cboEngNo]));

Here is the query which references that "subQuery":

SELECT [ContractCust] & " / " & [degno] & " / " & [CurConTp] & " / " & [GenericEngine] AS comb, [T-DEG].DEGno, [T-SideletterEng].SideletterEngID, [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp
FROM [Tp-Engmaster] RIGHT JOIN (((([T-ContractHdr] RIGHT JOIN [T-DEG] ON [T-ContractHdr].contractKEY = [T-DEG].ContractID) RIGHT JOIN [T-SideLetters] ON [T-DEG].DEGID = [T-SideLetters].degID) LEFT JOIN ([Tp-ContractType] RIGHT JOIN [T-ContractDetl] ON [Tp-ContractType].CurConTpID = [T-ContractDetl].CurConTpID) ON [T-SideLetters].SideletterID = [T-ContractDetl].SideLetterID) RIGHT JOIN (([T-SideletterEng] LEFT JOIN qry7_0exist ON [T-SideletterEng].SideletterEngID = qry7_0exist.SideletterEngID) INNER JOIN [Tp-EngNoToContract] ON [T-SideletterEng].SideletterEngID = [Tp-EngNoToContract].SideletterEngID) ON [T-SideLetters].SideletterID = [T-SideletterEng].SideLetterID) ON [Tp-Engmaster].EngID = [T-SideletterEng].engID
WHERE (((qry7_0exist.SideletterEngID) Is Null))
GROUP BY [T-DEG].DEGno, [T-SideletterEng].SideletterEngID, [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp
ORDER BY [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp;

The not in code:
SELECT [ContractCust] & " / " & [degno] & " / " & [CurConTp] & " / " & [GenericEngine] AS comb, [T-DEG].DEGno, [T-SideletterEng].SideletterEngID, [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp
FROM [Tp-Engmaster] RIGHT JOIN (((([T-ContractHdr] RIGHT JOIN [T-DEG] ON [T-ContractHdr].contractKEY = [T-DEG].ContractID) RIGHT JOIN [T-SideLetters] ON [T-DEG].DEGID = [T-SideLetters].degID) LEFT JOIN ([Tp-ContractType] RIGHT JOIN [T-ContractDetl] ON [Tp-ContractType].CurConTpID = [T-ContractDetl].CurConTpID) ON [T-SideLetters].SideletterID = [T-ContractDetl].SideLetterID) RIGHT JOIN ([T-SideletterEng] INNER JOIN [Tp-EngNoToContract] ON [T-SideletterEng].SideletterEngID = [Tp-EngNoToContract].SideletterEngID) ON [T-SideLetters].SideletterID = [T-SideletterEng].SideLetterID) ON [Tp-Engmaster].EngID = [T-SideletterEng].engID
WHERE ((([T-SideletterEng].SideletterEngID) not in (SELECT [T-SideletterEng].SideletterEngID, [Tp-EngNoToContract].EngRegID
FROM [T-SideletterEng] INNER JOIN [Tp-EngNoToContract] ON [T-SideletterEng].SideletterEngID = [Tp-EngNoToContract].SideletterEngID
WHERE ((([Tp-EngNoToContract].EngRegID) Like [forms]![frm7_0EngnoRegAssign]![cboEngNo]))))
GROUP BY [T-DEG].DEGno, [T-SideletterEng].SideletterEngID, [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp
ORDER BY [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp;

 

[ddiamond]

I found a few syntax problems with your not in version which I corrected. There might be other issues I haven't spotted yet, but why don't you give it a try:

SELECT [ContractCust] & " / " & [degno] & " / " & [CurConTp] & " / " & [GenericEngine] AS comb, [T-DEG].DEGno, [T-SideletterEng].SideletterEngID, [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp
FROM [Tp-Engmaster] RIGHT JOIN (((([T-ContractHdr] RIGHT JOIN [T-DEG] ON [T-ContractHdr].contractKEY = [T-DEG].ContractID) RIGHT JOIN [T-SideLetters] ON [T-DEG].DEGID = [T-SideLetters].degID) LEFT JOIN ([Tp-ContractType] RIGHT JOIN [T-ContractDetl] ON [Tp-ContractType].CurConTpID = [T-ContractDetl].CurConTpID) ON [T-SideLetters].SideletterID = [T-ContractDetl].SideLetterID) RIGHT JOIN ([T-SideletterEng] INNER JOIN [Tp-EngNoToContract] ON [T-SideletterEng].SideletterEngID = [Tp-EngNoToContract].SideletterEngID) ON [T-SideLetters].SideletterID = [T-SideletterEng].SideLetterID) ON [Tp-Engmaster].EngID = [T-SideletterEng].engID
WHERE ((([T-SideletterEng].SideletterEngID) not in (SELECT [T-SideletterEng].SideletterEngID
FROM [T-SideletterEng] INNER JOIN [Tp-EngNoToContract] ON [T-SideletterEng].SideletterEngID = [Tp-EngNoToContract].SideletterEngID
WHERE ((([Tp-EngNoToContract].EngRegID) Like [forms]![frm7_0EngnoRegAssign]![cboEngNo])))))
GROUP BY [T-DEG].DEGno, [T-SideletterEng].SideletterEngID, [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp
ORDER BY [T-ContractHdr].ContractCust, [Tp-Engmaster].GenericEngine, [Tp-ContractType].CurConTp;

 

[sub5]

Dan, fantastic, that's sorted it, cheers very much!