Multiple Sub Queries Coded for use in a main Query

[PatT123]

I received this reply to my former question. Can someone let me know if this
is true? Would it be possible if I cannot use a sub query within a query
that I could code this using a module on Open Form and update the Control
source with the results of the queries? I would rather put the queries
together if someone can clue me in how to format the queries properly. What
I don't want is to leave the queries in the query window, if possible.

In my experience (and I could be wrong),
you can use query SQL's as "virtual tables"
in the FROM clause, but the SQL cannot
contain any *further brackets* within it.

I don't think you are going to be able to use
SQL of qry1 in your full query as a "virtual table"
because it also uses a "virtual table."

Truly... and you're welcome to disregard...
but, when you go back to this db 6 months
from now after working on several other projects,
you will probably thank yourself if you save each
individual query, giving each a meaningful name,
then use the bottom query for your form....

good luck,

gary

I am having problems formatting a SQL Statement with Multiple joined
Queries.
The query works in three pieces as named queries, but I want to pull it
into
one query to put behind a form. Please help me find code that will help
me
with the structure.

Syntax Error in From Clause, but will save..

SELECT POSUpdt.PosNo, POSUpdt.Funding, POSUpdt.PCA, POSUpdt.Fund,
POSUpdt.FedGrant_PH, POSUpdt.OthGrant_PH, POSUpdt.Percent,
POSUpdt.SubProgram, POSUpdt.DIVFund, FTEUpdt.SumOfPercent, FTEUpdt.DIVFund
FROM

[SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram,
TBLIndex.DIV & TBLPCA.Fund AS DIVFund
FROM
(TBLIndex
RIGHT JOIN
TBLPosition
ON TBLIndex.Index = TBLPosition.Index)
RIGHT JOIN
((TBLFunds
RIGHT JOIN
TBLPCA
ON TBLFunds.FUND = TBLPCA.Fund)
RIGHT JOIN
(TBLFUNDING
RIGHT JOIN
TBLFUNDINGTOPOS
ON TBLFUNDING.Funding = TBLFUNDINGTOPOS.Funding)
ON TBLPCA.PCA = TBLFUNDING.PCA)
ON TBLPosition.PosNo = TBLFUNDINGTOPOS.PosNo]. AS POSUpdt

LEFT JOIN

[SELECT TBLFTETotals.FTETotal,
QRY1.SumOfPercent, TBLFTETotals.DIVFund
FROM
TBLFTETotals
LEFT JOIN

*****
you already started a "virtual table" above
which Access needs to enclose in brackets,
but will not tolerate any other brackets within
it...which, even though you typed in a parenthesis,
needs to be a bracket to start this next "inner virtual table"
*****

(SELECT Sum(TBLFundingToPos1.Percent) AS
SumOfPercent, TBLIndex1.DIV & TBLPCA1.Fund AS DIVFUND
FROM
(TBLIndex1
RIGHT JOIN
TBLPosition1
ON TBLIndex1.Index = TBLPosition1.Index)
RIGHT JOIN
((TBLFunding1
LEFT JOIN
TBLPCA1
ON TBLFunding1.PCA = TBLPCA1.PCA)
RIGHT JOIN
TBLFundingToPos1
ON TBLFunding1.Funding = TBLFundingToPos1.Funding)
ON TBLPosition1.PosNo = TBLFundingToPos1.PosNo
GROUP BY TBLIndex1.DIV, TBLPCA1.Fund). AS QRY1

ON
TBLFTETotals.DIVFund = QRY1.DIVFUND]. AS FTEUpdt
ON POSUpdt.DIVFund = FTEUpdt.DIVFund;

 

[PatT123]

I have progressed on this issue and reposted as Query Problem - Query Works
in Query Window. I put several of the fields in one of the tables to
simplify the query. It still does not work as code on the Form record
source. Can anyone help?

I received this reply to my former question. Can someone let me know if this
is true? Would it be possible if I cannot use a sub query within a query
that I could code this using a module on Open Form and update the Control
source with the results of the queries? I would rather put the queries
together if someone can clue me in how to format the queries properly. What
I don't want is to leave the queries in the query window, if possible.

In my experience (and I could be wrong),
you can use query SQL's as "virtual tables"
in the FROM clause, but the SQL cannot
contain any *further brackets* within it.

I don't think you are going to be able to use
SQL of qry1 in your full query as a "virtual table"
because it also uses a "virtual table."

Truly... and you're welcome to disregard...
but, when you go back to this db 6 months
from now after working on several other projects,
you will probably thank yourself if you save each
individual query, giving each a meaningful name,
then use the bottom query for your form....

good luck,

gary

I am having problems formatting a SQL Statement with Multiple joined
Queries.
The query works in three pieces as named queries, but I want to pull it
into
one query to put behind a form. Please help me find code that will help
me
with the structure.

Syntax Error in From Clause, but will save..

SELECT POSUpdt.PosNo, POSUpdt.Funding, POSUpdt.PCA, POSUpdt.Fund,
POSUpdt.FedGrant_PH, POSUpdt.OthGrant_PH, POSUpdt.Percent,
POSUpdt.SubProgram, POSUpdt.DIVFund, FTEUpdt.SumOfPercent, FTEUpdt.DIVFund
FROM

[SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram,
TBLIndex.DIV & TBLPCA.Fund AS DIVFund
FROM
(TBLIndex
RIGHT JOIN
TBLPosition
ON TBLIndex.Index = TBLPosition.Index)
RIGHT JOIN
((TBLFunds
RIGHT JOIN
TBLPCA
ON TBLFunds.FUND = TBLPCA.Fund)
RIGHT JOIN
(TBLFUNDING
RIGHT JOIN
TBLFUNDINGTOPOS
ON TBLFUNDING.Funding = TBLFUNDINGTOPOS.Funding)
ON TBLPCA.PCA = TBLFUNDING.PCA)
ON TBLPosition.PosNo = TBLFUNDINGTOPOS.PosNo]. AS POSUpdt

LEFT JOIN

[SELECT TBLFTETotals.FTETotal,
QRY1.SumOfPercent, TBLFTETotals.DIVFund
FROM
TBLFTETotals
LEFT JOIN

*****
you already started a "virtual table" above
which Access needs to enclose in brackets,
but will not tolerate any other brackets within
it...which, even though you typed in a parenthesis,
needs to be a bracket to start this next "inner virtual table"
*****

(SELECT Sum(TBLFundingToPos1.Percent) AS
SumOfPercent, TBLIndex1.DIV & TBLPCA1.Fund AS DIVFUND
FROM
(TBLIndex1
RIGHT JOIN
TBLPosition1
ON TBLIndex1.Index = TBLPosition1.Index)
RIGHT JOIN
((TBLFunding1
LEFT JOIN
TBLPCA1
ON TBLFunding1.PCA = TBLPCA1.PCA)
RIGHT JOIN
TBLFundingToPos1
ON TBLFunding1.Funding = TBLFundingToPos1.Funding)
ON TBLPosition1.PosNo = TBLFundingToPos1.PosNo
GROUP BY TBLIndex1.DIV, TBLPCA1.Fund). AS QRY1

ON
TBLFTETotals.DIVFund = QRY1.DIVFUND]. AS FTEUpdt
ON POSUpdt.DIVFund = FTEUpdt.DIVFund;

Queries as Named in the Database. It does run. Cannot replace names with
Code.

SELECT Query3.PosNo, Query3.Funding, Query3.PCA, Query3.Fund,
Query3.FedGrant_PH, Query3.OthGrant_PH, Query3.Percent, Query3.SubProgram,
Query3.DIVFund, Query2.SumOfPercent, Query2.DIVFund
FROM Query3 LEFT JOIN Query2 ON Query3.DIVFund = Query2.DIVFund;



Thank You
Pat