Problem with a query that has multiple queries in it.

P

PatT123

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 (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
 
G

Gary Walter

Hi Pat,

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

PatT123 said:
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
Click to expand...
[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;
 
P

PatT123

I have progressed on this issue and put some of the fields in a table. Still
having problems. Reposted as Query Problem Query works in Query Window.

Thank you.

Gary Walter said:
Hi Pat,

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

PatT123 said:
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
Click to expand...
[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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Sub Queries Coded for use in a main Query 1
Problem with a query that has multiple queries within it. 0
Query Problem - Query works in Query window but not as code in a f 2
Problem writing code of query into a form 1
Problem putting a SQL Statement into a form record source. 2

Top