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];
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];