Here's a challenge for you all: Nesting sub-queries...

[TheInsider]

Hi,<br>&nbsp;In order to work around the limitations of an ActiveX control, I need to create one main SQL query that nests several aggregate sub-queries. The sub-queries cannot be referrenced query-defs, though, but they do have to have a relationship. In layman's terms, I have some pre-saved sub-queries that have a relational join in their parent query. I need these sub-queries converted into one big query.<br>Listed below are the queries:<br><br>1) The Parent/Main query:<br><br>SELECT tbdFunds.fldFundName, [fldLastName] & &quot;, &quot; & [fldFirstName] & &quot; &quot; & [fldMiddleInitial] AS OwnerName, tbdClientFunds.fldContractNumber, qryLastUnitsOwned2.fldUnitsOwned, qryLastReset.MaxOffldDateOfFreeze, qryLastUnitValue2.fldUnitValue, [fldUnitsOwned]*[fldUnitValue] AS TotalValue<br>FROM tbdFunds RIGHT JOIN (tbdClients LEFT JOIN ((((tbdClientContracts LEFT JOIN tbdClientFunds ON tbdClientContracts.fldContractNumber = tbdClientFunds.fldContractNumber) LEFT JOIN qryLastUnitValue2 ON (tbdClientFunds.fldContractNumber = qryLastUnitValue2.fldContractNumber) AND (tbdClientFunds.fldFundID = qryLastUnitValue2.fldFundID)) LEFT JOIN qryLastReset ON (tbdClientFunds.fldContractNumber = qryLastReset.fldContractNumber) AND (tbdClientFunds.fldFundID = qryLastReset.fldFundID)) LEFT JOIN qryLastUnitsOwned2 ON (tbdClientFunds.fldFundID = qryLastUnitsOwned2.fldFundID) AND (tbdClientFunds.fldContractNumber = qryLastUnitsOwned2.fldContractNumber)) ON tbdClients.fldClientID = tbdClientContracts.fldClientID) ON tbdFunds.fldFundID = tbdClientFunds.fldFundID;<br><br>2) [qryLastReset]:<br><br>SELECT tbdFrozenFunds.fldContractNumber, tbdFrozenFunds.fldFundID, Max(tbdFrozenFunds.fldDateOfFreeze) AS MaxOffldDateOfFreeze<br>FROM tbdFrozenFunds<br>GROUP BY tbdFrozenFunds.fldContractNumber, tbdFrozenFunds.fldFundID;<br><br><br>3) [qryLastUnitValue1]:<br><br>SELECT qryLastReset.fldContractNumber, qryLastReset.fldFundID, Max(tbdFundValue.fldDateOfEntry) AS MaxOffldDateOfEntry<br>FROM tbdFundValue RIGHT JOIN qryLastReset ON tbdFundValue.fldFundID = qryLastReset.fldFundID<br>WHERE (((tbdFundValue.fldDateOfEntry)&lt;=[MaxOffldDateOfFreeze]))<br>GROUP BY qryLastReset.fldContractNumber, qryLastReset.fldFundID;<br><br>4) [qryLastUnitValue2]:<br><br>SELECT qryLastUnitValue1.fldContractNumber, qryLastUnitValue1.fldFundID, tbdFundValue.fldUnitValue<br>FROM tbdFundValue RIGHT JOIN qryLastUnitValue1 ON tbdFundValue.fldFundID = qryLastUnitValue1.fldFundID<br>WHERE (((tbdFundValue.fldDateOfEntry)=[MaxOffldDateOfEntry]));<br><br>5) [qryLastUnitsOwned1]:<br><br>SELECT tbdUnitsOwned.fldContractNumber, tbdUnitsOwned.fldFundID, Max(tbdUnitsOwned.fldDateOfEntry) AS MaxOffldDateOfEntry<br>FROM tbdUnitsOwned<br>GROUP BY tbdUnitsOwned.fldContractNumber, tbdUnitsOwned.fldFundID;<br><br>6) [qryLastUnitsOwned2]:<br><br>SELECT tbdUnitsOwned.fldContractNumber, tbdUnitsOwned.fldFundID, tbdUnitsOwned.fldUnitsOwned<br>FROM qryLastUnitsOwned1 LEFT JOIN tbdUnitsOwned ON (qryLastUnitsOwned1.fldContractNumber = tbdUnitsOwned.fldContractNumber) AND (qryLastUnitsOwned1.fldFundID = tbdUnitsOwned.fldFundID)<br>WHERE (((tbdUnitsOwned.fldDateOfEntry)=[MaxOffldDateOfEntry]));<br><br>If someone could give me some idea how to combine these, I would be very happy!<br>Thanks,<br>Rob

 

[MalcolmW]

I'm too lazy to read in detail what you want to do, but it appears that you could join your sub queries as derived tables.<br>ie<br>SELECT ...<br>FROM ...your main query...<br>JOIN<br>(SELECT ... another query ... FROM ... GROUP&nbsp;&nbsp;BY ...HAVING...) AS Q2 ON (...your linking conditions...)<br>JOIN<br>etc for the other queries. <br><br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>