Feild could refer to more than one table?


J

John T Ingato

Can someone tell me what is wrong with this SQL. When I try to run it I get
an error:
The specified field '[Current Cost]" could refer to to more than one table
ilisted n the FROM clause of your SQL statement.

I don't see it. It looks to me that I have every field diectly attached to
a table.

SELECT tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
Sum(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS [YTD Units],
Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS ASW,
Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory], Format([YTD
Units]*[Current Retail],"Currency") AS [Retail $], Format([YTD
Units]*[Current Cost],"$#,###") AS [Cost $], tblProgramHomeDepot.[Current
Cost], tblProgramHomeDepot.[Current Retail], ([YTD Units]/([Avg
Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported]
FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot INNER
JOIN tblProductLine ON
tblCalculatedSalesDataHomeDepot.ItemNumber=tblProductLine.ItemNumber) ON
(tblHomeDepotPOSData.StoreNumber=tblCalculatedSalesDataHomeDepot.StoreNumber)
AND
(tblHomeDepotPOSData.SKUNumber=tblCalculatedSalesDataHomeDepot.SKUNumber)
AND (tblCalculatedSalesDataHomeDepot.ToDate=tblHomeDepotPOSData.ReportDate))
INNER JOIN tblProgramHomeDepot ON
tblProductLine.ItemNumber=tblProgramHomeDepot.ItemNumber
GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail];
 
Ad

Advertisements

6

'69 Camaro

Hi, John.
I don't see it.
Found it:

Format([YTD Units]*[Current Cost],"$#,###") AS [Cost $]

Change it to:

Format([YTD Units] * tblProgramHomeDepot.[Current Cost],"$#,###") AS
[Cost $]

.. . . or use the name of one of the other tables that contains Current Cost,
whichever is appropriate.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


John T Ingato said:
Can someone tell me what is wrong with this SQL. When I try to run it I
get an error:
The specified field '[Current Cost]" could refer to to more than one table
ilisted n the FROM clause of your SQL statement.

I don't see it. It looks to me that I have every field diectly attached
to a table.

SELECT tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
Sum(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS [YTD Units],
Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS ASW,
Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory], Format([YTD
Units]*[Current Retail],"Currency") AS [Retail $], Format([YTD
Units]*[Current Cost],"$#,###") AS [Cost $], tblProgramHomeDepot.[Current
Cost], tblProgramHomeDepot.[Current Retail], ([YTD Units]/([Avg
Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported]
FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot
INNER JOIN tblProductLine ON
tblCalculatedSalesDataHomeDepot.ItemNumber=tblProductLine.ItemNumber) ON
(tblHomeDepotPOSData.StoreNumber=tblCalculatedSalesDataHomeDepot.StoreNumber)
AND
(tblHomeDepotPOSData.SKUNumber=tblCalculatedSalesDataHomeDepot.SKUNumber)
AND
(tblCalculatedSalesDataHomeDepot.ToDate=tblHomeDepotPOSData.ReportDate))
INNER JOIN tblProgramHomeDepot ON
tblProductLine.ItemNumber=tblProgramHomeDepot.ItemNumber
GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail];
 
Ad

Advertisements

J

John T Ingato

OMG - You are right. Thanks. I swear I went through it several times.

I thank you again, even though my favorite car is the 67 Firebird. All
least we are both GM

'69 Camaro said:
Hi, John.
I don't see it.
Found it:

Format([YTD Units]*[Current Cost],"$#,###") AS [Cost $]

Change it to:

Format([YTD Units] * tblProgramHomeDepot.[Current Cost],"$#,###") AS
[Cost $]

. . . or use the name of one of the other tables that contains Current
Cost, whichever is appropriate.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


John T Ingato said:
Can someone tell me what is wrong with this SQL. When I try to run it I
get an error:
The specified field '[Current Cost]" could refer to to more than one
table ilisted n the FROM clause of your SQL statement.

I don't see it. It looks to me that I have every field diectly attached
to a table.

SELECT tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber,
tblProductLine.SkuDescription, Sum(tblCalculatedSalesDataHomeDepot.[Adj
Sales]) AS [YTD Units], Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales])
AS ASW, Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory], Format([YTD
Units]*[Current Retail],"Currency") AS [Retail $], Format([YTD
Units]*[Current Cost],"$#,###") AS [Cost $], tblProgramHomeDepot.[Current
Cost], tblProgramHomeDepot.[Current Retail], ([YTD Units]/([Avg
Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported]
FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot
INNER JOIN tblProductLine ON
tblCalculatedSalesDataHomeDepot.ItemNumber=tblProductLine.ItemNumber) ON
(tblHomeDepotPOSData.StoreNumber=tblCalculatedSalesDataHomeDepot.StoreNumber)
AND
(tblHomeDepotPOSData.SKUNumber=tblCalculatedSalesDataHomeDepot.SKUNumber)
AND
(tblCalculatedSalesDataHomeDepot.ToDate=tblHomeDepotPOSData.ReportDate))
INNER JOIN tblProgramHomeDepot ON
tblProductLine.ItemNumber=tblProgramHomeDepot.ItemNumber
GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber,
tblProductLine.SkuDescription, tblProgramHomeDepot.[Current Cost],
tblProgramHomeDepot.[Current Retail];
 

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

Top