I have the code below that was created in SQL view by someone else to help me out of a problem I was having. The code worked well until, when adding a new feature to my form, I had to change field types in my main table, "tblProductionNumbers", from Number type (because I was using lookups) to text type. I had to use text type for the new feature because even though the values look like times they included the values of "end-days" and "end-nights" I was getting a type mismatch error because the expected field value was a number (lookup) but the answer was a text answer.
I had numerous other queries that were created in designer view (by me) that I could correct by changing the joins as shown in the picture for qryWeeklySumTotalLFProducedSubquery. Also included is a picture to show that the SQL code in question looks like in designer mode if that helps.
My problem queries were created in SQL view and a temp table is created to handle some of the math. I'm lost as to how to change it to get it to work again. I'm hoping someone here can help. If I can see how one of my problem queries is fixed I can fix the others by using the same logic.
Chris
qryWeeklySumTotalLFProducedSubquery
https://docs.google.com/open?id=0Bz1gle1F6-ytSzJYUG1EdW9nVW8
qryWeeklyWastePercentLineChartonFormSubquery
https://docs.google.com/open?id=0Bz1gle1F6-yta013VUlrX3ktcjQ
I had numerous other queries that were created in designer view (by me) that I could correct by changing the joins as shown in the picture for qryWeeklySumTotalLFProducedSubquery. Also included is a picture to show that the SQL code in question looks like in designer mode if that helps.
My problem queries were created in SQL view and a temp table is created to handle some of the math. I'm lost as to how to change it to get it to work again. I'm hoping someone here can help. If I can see how one of my problem queries is fixed I can fix the others by using the same logic.
Chris
qryWeeklySumTotalLFProducedSubquery
https://docs.google.com/open?id=0Bz1gle1F6-ytSzJYUG1EdW9nVW8
qryWeeklyWastePercentLineChartonFormSubquery
https://docs.google.com/open?id=0Bz1gle1F6-yta013VUlrX3ktcjQ
Code:
SELECT tblProductList.[Part Number], g.Sum_Of_LF_Produced, g.Sum_Of_LF_Run, g.Sum_Of_LF_Run-g.Sum_Of_LF_Produced AS Waste, ([g].[Sum_Of_LF_Run]-[g].[Sum_Of_LF_Produced])/[g].[Sum_Of_LF_Run] AS Waste_Percent
FROM (tblProductList INNER JOIN (SELECT tblProductionNumbers.ProductID,
Sum(tblProductionNumbers.[LF Run]) AS Sum_Of_LF_Run,
Sum(tblProductionNumbers.[LF Produced]) AS Sum_Of_LF_Produced
FROM tblProductionHours
INNER JOIN (tblProductList
INNER JOIN tblProductionNumbers ON tblProductList.ID = tblProductionNumbers.ProductID)
ON tblProductionHours.ID = tblProductionNumbers.TimeID
WHERE ((tblProductionHours.Time In ("END-Days","End-Nights")) AND
(DatePart("ww",[ProductionDate])=DatePart("ww",Date())) AND
(Year([ProductionDate])=Year(Date()))) OR ((DatePart("ww",[ProductionDate])="*"))
GROUP BY tblProductionNumbers.ProductID
) AS g ON tblProductList.ID = g.ProductID) INNER JOIN (tblProductionHours INNER JOIN tblProductionNumbers ON tblProductionHours.ID = tblProductionNumbers.TimeID) ON tblProductList.ID = tblProductionNumbers.ProductID
GROUP BY tblProductList.[Part Number], g.Sum_Of_LF_Produced, g.Sum_Of_LF_Run, g.Sum_Of_LF_Run-g.Sum_Of_LF_Produced;