Feild could refer to more than one table?

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

 

['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.

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

 

[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

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.

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