Where to put Where Statement

[lipin]

I have inherited this DB and need to make changes.

The code below is summing warehouse production by Dept for me. It is selecting records from tblHistory and giving me a prior 12 week actuals which is then the goal for the week. Well I only need fully trained people included in the goal.
There is a field in tblHistory called 'Training' and i only want records with 'N' in that field.
I was going to insert a where statement into the following but don't know exactly where it would go.
Something like: WHERE tblHistory.Training = N
Would that do it?





DoCmd.RunSQL ("INSERT INTO tblDeptGoalSample ( WeekStartDate, WeekEndDate, DeptCode, DeptDescription, DeptHrs, DeptUnits, DeptCartons ) " & _
"SELECT CDate(#" & GoalWeek(x).StartDate & "#), " & _
"CDate(#" & GoalWeek(x).EndDate & "#), " & _
"FIRST(tblActiveJobCd.JobGrp), " & _
"FIRST(tblActiveJobCd.Description), " & _
"SUM(tblHistory.StdHrs) AS SumOfStdHrs, " & _
"SUM(tblHistory.TotalUnit) As SumOfTotalUnit, " & _
"SUM(tblHistory.TotalCtn) As SumOfTotalCtn " & _
"FROM tblHistory " & _
"INNER JOIN tblActiveJobCd ON tblActiveJobCd.JobCd = tblHistory.JobCd " & _
"WHERE EntryDate >= #" & GoalWeek(x).StartDate & "# " & _
"AND EntryDate <= #" & GoalWeek(x).EndDate & "# " & _
"GROUP BY tblHistory.JobCd " & _
"ORDER BY tblHistory.JobCd")

 

[sportsguy]

Two possible answers

DoCmd.RunSQL ("INSERT INTO tblDeptGoalSample ( WeekStartDate, WeekEndDate, DeptCode, DeptDescription, DeptHrs, DeptUnits, DeptCartons ) " & _
"SELECT CDate(#" & GoalWeek(x).StartDate & "#), " & _
"CDate(#" & GoalWeek(x).EndDate & "#), " & _
"FIRST(tblActiveJobCd.JobGrp), " & _
"FIRST(tblActiveJobCd.Description), " & _
"SUM(tblHistory.StdHrs) AS SumOfStdHrs, " & _
"SUM(tblHistory.TotalUnit) As SumOfTotalUnit, " & _
"SUM(tblHistory.TotalCtn) As SumOfTotalCtn " & _
"FROM tblHistory " & _
"INNER JOIN tblActiveJobCd ON tblActiveJobCd.JobCd = tblHistory.JobCd " & _
"WHERE ((EntryDate >= #" & GoalWeek(x).StartDate & "#) AND " & _
"(EntryDate <= #" & GoalWeek(x).EndDate & "# ") AND (tblHistory.Training = "N" ))" & _
"GROUP BY tblHistory.JobCd " & _
"ORDER BY tblHistory.JobCd")

if the Training Field is Text, 1 Character, then "N", If the Training Field is Yes/No, then "N" would be No,

and the key to the Where clause is the parenthesis in the proper place and with the proper number of them

The other step you might take is to copy the query starting with INSERT all the way to the end of the query, and paste it into the SQL view of a blank query, then manually remove all the " & _ so that the query can compile into the QBE, and then add the where clause yourself, flip back to SQL, and paste the WHERE clause into the DoCmd. code.

sportsguy

 

[FoFa]

"WHERE EntryDate >= #" & GoalWeek(x).StartDate & "# " & _
"AND EntryDate <= #" & GoalWeek(x).EndDate & "# " & _
"AND tblHistory.Training = 'N'" & _
"GROUP BY tblHistory.JobCd " & _

 

[sportsguy]

yeah, the 'N' is more correct.

sportsguy

 

[lipin]

Thanks! That Got It!!

Which leads me to another problem. Mgmt wants just 'N' employees in all depts except two. In Dept 100 and Dept 1500 they want all employees.

So in the alredy present code is there an easy was to say: If Dept <>100 and <> 1500 then "AND tblHistory.Train = 'N'"

Sorry for dragging on with this.

 

[sportsguy]

This is where the () come in handy to separate difference searches

"WHERE (((EntryDate >= #" & GoalWeek(x).StartDate & "#) AND (EntryDate <= #" & GoalWeek(x).EndDate & "# ") AND (tblHistory.Training = 'N' )) " & _
OR (((EntryDate >= #" & GoalWeek(x).StartDate & "#) AND (EntryDate <= #" & GoalWeek(x).EndDate & "# ") AND ((tblHistory.Dept = 100) OR (tblHistory.Dept = 1500))) " & _

You will have to break up the code into rows as you see fit. . .

if the code doesn't compile correctly, then the ()s need adjusting. . .
there are too many concatenations for me to read clearly. . . .
old, blind soccer referee. . .

sportsguy