If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Feild could refer to more than one table?
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=tblProd uctLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber=tblCalculatedSale sDataHomeDepot.StoreNumber) AND (tblHomeDepotPOSData.SKUNumber=tblCalculatedSalesD ataHomeDepot.SKUNumber) AND (tblCalculatedSalesDataHomeDepot.ToDate=tblHomeDep otPOSData.ReportDate)) INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber=tblProgramHomeDepot.Item Number GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber, tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription, tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail]; |
#2
|
|||
|
|||
Feild could refer to more than one table?
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/ex...ributors2.html for contact info. "John T Ingato" wrote in message ... 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=tblProd uctLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber=tblCalculatedSale sDataHomeDepot.StoreNumber) AND (tblHomeDepotPOSData.SKUNumber=tblCalculatedSalesD ataHomeDepot.SKUNumber) AND (tblCalculatedSalesDataHomeDepot.ToDate=tblHomeDep otPOSData.ReportDate)) INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber=tblProgramHomeDepot.Item Number GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber, tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription, tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail]; |
#3
|
|||
|
|||
Feild could refer to more than one table?
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" AM wrote in message ... 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/ex...ributors2.html for contact info. "John T Ingato" wrote in message ... 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=tblProd uctLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber=tblCalculatedSale sDataHomeDepot.StoreNumber) AND (tblHomeDepotPOSData.SKUNumber=tblCalculatedSalesD ataHomeDepot.SKUNumber) AND (tblCalculatedSalesDataHomeDepot.ToDate=tblHomeDep otPOSData.ReportDate)) INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber=tblProgramHomeDepot.Item Number GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber, tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription, tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail]; |
Thread Tools | |
Display Modes | |
|
|