ms access the specified field could refer to more than one table in the FROM clause of your SQL stat

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, tblPTCContractInfo.OrigContract,
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.
 
G

Guest

When you get this error it meas you have the same field name from two
different tables, queries, or table & query combination.
To fix it you have to include the table or query name with the field name.
--
KARL DEWEY
Build a little - Test a little


Todd H said:
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, tblPTCContractInfo.OrigContract,
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.
Click to expand...
 
T

Todd H

When you get this error it meas you have the same field name from two
different tables, queries, or table & query combination.
To fix it you have to include the table or query name with the field name.
Click to expand...

Thanks for the input; it was actually an error in one of the report
expressions that I didn't catch. I did have a duplicate field name
that I was drawing from two of my queries. It helped tremendously to
rename the like fields in the individual queries; it made building the
expressions in the reports infinitely simplier.

Todd
 

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

specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen 1
The specified field could refer to more than one table listed in theFROM clause of your SQL statemen 5
Wicked error- could refer to more than one table in the FROM 1
Windows XP The specified table could refer to more than one table in the FROM clause of your SQL 4
The specified field name could refer to more than one table listed in the from clause of your SQL st 1

Top