Help correcting SQL code (1 Viewer)

[Repent]

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

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;

 

[Pat Hartman]

Getting rid of the lookups didn't mean that you should be storing the "lookup" value instead of the numeric ID. Change the table back to the correct structure. Use lookups on forms NOT on tables.

FYI - Jet/ACE can be slow with subqueries. Most subqueries can be replaced by joins to tables or queries and will give better performance.

 

[spikepl]

So now you just take one of your backup copies, that you made before introducing any major changes, and work from that. You do have one, right?

 

[Repent]

So now you just take one of your backup copies, that you made before introducing any major changes, and work from that. You do have one, right?

Oh you bet I do!! I'm of the belief that one can never have too many backups of projects. I always make a backup every morning before my daily development as well as backups before I change anything major.

Yeah, I was thinking the same thing based on the feedback to my post.

chris