T
Todd H
I get the following error when running a report: The specified field
'qryBudget.SumOfEstimate' could refer to more than one table in the
FROM clause of your SQL statement.
I have a nested query that pulls data together to make a Budget
Control Sheet. The 'qryBudget.SumOfEstimate' is the "Budget" for each
line. What I am trying to do is show one "Interest Income Line" at
the conclusion of the report. I created a seperate query
qryBudgetControlReportINTERESTINCOMELINE that filters the one Interest
Income Line that I want to seperate and then inserted into my original
query qryBudgetControlQuery, where I filter out the same line so that
it doesnt appear in the body of the report. The
qryBudgetControlReport runs fine and gives me the data I want, but I
continue to get the error when I try to run the report. Help!!!
Here is the SQL for qryBudgetControl:
SELECT DISTINCTROW tblEstimateLineItems.Phase,
tblProjectPhase.PhaseLong, tblEstimateLineItems.LineItem,
qryFutureCosts.Category, tblEstimateLineItems.BidPackageTitle,
qryBudget.Budget, tblEstimateLineItems.NoContract,
qryActualAndProjectedNoContractCost.SumOfActualCost,
qryActualAndProjectedNoContractCost.SumOfProjectedCost,
tblPTCContractInfo.PTCResp, tblPTCContractInfrigContract,
qryCOTS.SumOfPCOEstimate, qryCOTS.SumOfPCOFinalCost,
tblProjectInfo.ProjNameLong, tblProjectInfo.ProjNo,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeTitle,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeBudget,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeActual,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeProj
FROM tblProjectInfo, qryBudgetControlReportINTERESTINCOMELINE,
tblPTCContractInfo INNER JOIN (((((tblEstimateLineItems INNER JOIN
qryCOTS ON tblEstimateLineItems.LineItem = qryCOTS.LineItem) INNER
JOIN qryBudget ON tblEstimateLineItems.LineItem = qryBudget.LineItem)
INNER JOIN qryFutureCosts ON tblEstimateLineItems.LineItem =
qryFutureCosts.LineItem) INNER JOIN tblProjectPhase ON
tblEstimateLineItems.Phase = tblProjectPhase.Phase) INNER JOIN
qryActualAndProjectedNoContractCost ON tblEstimateLineItems.LineItem =
qryActualAndProjectedNoContractCost.LineItem) ON
tblPTCContractInfo.ScopePackageNo =
tblEstimateLineItems.ScopePackageNo
ORDER BY tblEstimateLineItems.Phase, tblEstimateLineItems.LineItem,
qryFutureCosts.Category;
and here is qryBudgetControlReportINTERESTINCOMELINE:
SELECT DISTINCTROW tblEstimateLineItems.BidPackageTitle AS
InterestIncomeTitle, qryBudget.Budget AS InterestIncomeBudget,
qryActualAndProjectedNoContractCost.SumOfActualCost AS
InterestIncomeActual,
qryActualAndProjectedNoContractCost.SumOfProjectedCost AS
InterestIncomeProj, tblEstimateLineItems.LineItem
FROM tblProjectInfo, tblPTCContractInfo INNER JOIN
((((tblEstimateLineItems INNER JOIN qryCOTS ON
tblEstimateLineItems.LineItem = qryCOTS.LineItem) INNER JOIN qryBudget
ON tblEstimateLineItems.LineItem = qryBudget.LineItem) INNER JOIN
tblProjectPhase ON tblEstimateLineItems.Phase = tblProjectPhase.Phase)
INNER JOIN qryActualAndProjectedNoContractCost ON
tblEstimateLineItems.LineItem =
qryActualAndProjectedNoContractCost.LineItem) ON
tblPTCContractInfo.ScopePackageNo =
tblEstimateLineItems.ScopePackageNo
WHERE (((tblEstimateLineItems.LineItem)="G01045"))
ORDER BY tblEstimateLineItems.LineItem;
And here is qryBudget for reference:
SELECT tblEstimateDetail.[Estimate Name], qryAllLines.LineItem,
tblEstimateDetail.Estimate AS Budget
FROM qryAllLines INNER JOIN tblEstimateDetail ON qryAllLines.LineItem
= tblEstimateDetail.LineItem
GROUP BY tblEstimateDetail.[Estimate Name], qryAllLines.LineItem,
tblEstimateDetail.Estimate
HAVING (((tblEstimateDetail.[Estimate Name])="Budget"));
Thanks for the help.
'qryBudget.SumOfEstimate' could refer to more than one table in the
FROM clause of your SQL statement.
I have a nested query that pulls data together to make a Budget
Control Sheet. The 'qryBudget.SumOfEstimate' is the "Budget" for each
line. What I am trying to do is show one "Interest Income Line" at
the conclusion of the report. I created a seperate query
qryBudgetControlReportINTERESTINCOMELINE that filters the one Interest
Income Line that I want to seperate and then inserted into my original
query qryBudgetControlQuery, where I filter out the same line so that
it doesnt appear in the body of the report. The
qryBudgetControlReport runs fine and gives me the data I want, but I
continue to get the error when I try to run the report. Help!!!
Here is the SQL for qryBudgetControl:
SELECT DISTINCTROW tblEstimateLineItems.Phase,
tblProjectPhase.PhaseLong, tblEstimateLineItems.LineItem,
qryFutureCosts.Category, tblEstimateLineItems.BidPackageTitle,
qryBudget.Budget, tblEstimateLineItems.NoContract,
qryActualAndProjectedNoContractCost.SumOfActualCost,
qryActualAndProjectedNoContractCost.SumOfProjectedCost,
tblPTCContractInfo.PTCResp, tblPTCContractInfrigContract,
qryCOTS.SumOfPCOEstimate, qryCOTS.SumOfPCOFinalCost,
tblProjectInfo.ProjNameLong, tblProjectInfo.ProjNo,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeTitle,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeBudget,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeActual,
qryBudgetControlReportINTERESTINCOMELINE.InterestIncomeProj
FROM tblProjectInfo, qryBudgetControlReportINTERESTINCOMELINE,
tblPTCContractInfo INNER JOIN (((((tblEstimateLineItems INNER JOIN
qryCOTS ON tblEstimateLineItems.LineItem = qryCOTS.LineItem) INNER
JOIN qryBudget ON tblEstimateLineItems.LineItem = qryBudget.LineItem)
INNER JOIN qryFutureCosts ON tblEstimateLineItems.LineItem =
qryFutureCosts.LineItem) INNER JOIN tblProjectPhase ON
tblEstimateLineItems.Phase = tblProjectPhase.Phase) INNER JOIN
qryActualAndProjectedNoContractCost ON tblEstimateLineItems.LineItem =
qryActualAndProjectedNoContractCost.LineItem) ON
tblPTCContractInfo.ScopePackageNo =
tblEstimateLineItems.ScopePackageNo
ORDER BY tblEstimateLineItems.Phase, tblEstimateLineItems.LineItem,
qryFutureCosts.Category;
and here is qryBudgetControlReportINTERESTINCOMELINE:
SELECT DISTINCTROW tblEstimateLineItems.BidPackageTitle AS
InterestIncomeTitle, qryBudget.Budget AS InterestIncomeBudget,
qryActualAndProjectedNoContractCost.SumOfActualCost AS
InterestIncomeActual,
qryActualAndProjectedNoContractCost.SumOfProjectedCost AS
InterestIncomeProj, tblEstimateLineItems.LineItem
FROM tblProjectInfo, tblPTCContractInfo INNER JOIN
((((tblEstimateLineItems INNER JOIN qryCOTS ON
tblEstimateLineItems.LineItem = qryCOTS.LineItem) INNER JOIN qryBudget
ON tblEstimateLineItems.LineItem = qryBudget.LineItem) INNER JOIN
tblProjectPhase ON tblEstimateLineItems.Phase = tblProjectPhase.Phase)
INNER JOIN qryActualAndProjectedNoContractCost ON
tblEstimateLineItems.LineItem =
qryActualAndProjectedNoContractCost.LineItem) ON
tblPTCContractInfo.ScopePackageNo =
tblEstimateLineItems.ScopePackageNo
WHERE (((tblEstimateLineItems.LineItem)="G01045"))
ORDER BY tblEstimateLineItems.LineItem;
And here is qryBudget for reference:
SELECT tblEstimateDetail.[Estimate Name], qryAllLines.LineItem,
tblEstimateDetail.Estimate AS Budget
FROM qryAllLines INNER JOIN tblEstimateDetail ON qryAllLines.LineItem
= tblEstimateDetail.LineItem
GROUP BY tblEstimateDetail.[Estimate Name], qryAllLines.LineItem,
tblEstimateDetail.Estimate
HAVING (((tblEstimateDetail.[Estimate Name])="Budget"));
Thanks for the help.