gnod
02-12-2007, 08:13 AM
Hi,
is there any other way to simply the WHERE clause.. from wk 4 to wk 52 should not be zero(0)..
strSQL_Consol = "SELECT RefCode, "
strSQL_Consol = strSQL_Consol & "Sum([wk 1]) AS SumOfwk1, Sum([wk 2]) AS SumOfwk2, Sum([wk 3]) AS SumOfwk3, Sum([wk 4]) AS SumOfwk4, "
strSQL_Consol = strSQL_Consol & "Sum([wk 5]) AS SumOfwk5, Sum([wk 6]) AS SumOfwk6, Sum([wk 7]) AS SumOfwk7, Sum([wk 8]) AS SumOfwk8, "
strSQL_Consol = strSQL_Consol & "Sum([wk 9]) AS SumOfwk9, Sum([wk 10]) AS SumOfwk10, Sum([wk 11]) AS SumOfwk11, Sum([wk 12]) AS SumOfwk12, "
strSQL_Consol = strSQL_Consol & "Sum([wk 13]) AS SumOfwk13, Sum([wk 14]) AS SumOfwk14, Sum([wk 15]) AS SumOfwk15, Sum([wk 16]) AS SumOfwk16, "
strSQL_Consol = strSQL_Consol & "Sum([wk 17]) AS SumOfwk17, Sum([wk 18]) AS SumOfwk18, Sum([wk 19]) AS SumOfwk19, Sum([wk 20]) AS SumOfwk20, "
strSQL_Consol = strSQL_Consol & "Sum([wk 21]) AS SumOfwk21, Sum([wk 22]) AS SumOfwk22, Sum([wk 23]) AS SumOfwk23, Sum([wk 24]) AS SumOfwk24, "
strSQL_Consol = strSQL_Consol & "Sum([wk 25]) AS SumOfwk25, Sum([wk 26]) AS SumOfwk26, Sum([wk 27]) AS SumOfwk27, Sum([wk 28]) AS SumOfwk28, "
strSQL_Consol = strSQL_Consol & "Sum([wk 29]) AS SumOfwk29, Sum([wk 30]) AS SumOfwk30, Sum([wk 31]) AS SumOfwk31, Sum([wk 32]) AS SumOfwk32, "
strSQL_Consol = strSQL_Consol & "Sum([wk 33]) AS SumOfwk33, Sum([wk 34]) AS SumOfwk34, Sum([wk 35]) AS SumOfwk35, Sum([wk 36]) AS SumOfwk36, "
strSQL_Consol = strSQL_Consol & "Sum([wk 37]) AS SumOfwk37, Sum([wk 38]) AS SumOfwk38, Sum([wk 39]) AS SumOfwk39, Sum([wk 40]) AS SumOfwk40, "
strSQL_Consol = strSQL_Consol & "Sum([wk 41]) AS SumOfwk41, Sum([wk 42]) AS SumOfwk42, Sum([wk 43]) AS SumOfwk43, Sum([wk 44]) AS SumOfwk44, "
strSQL_Consol = strSQL_Consol & "Sum([wk 45]) AS SumOfwk45, Sum([wk 46]) AS SumOfwk46, Sum([wk 47]) AS SumOfwk47, Sum([wk 48]) AS SumOfwk48, "
strSQL_Consol = strSQL_Consol & "Sum([wk 49]) AS SumOfwk49, Sum([wk 50]) AS SumOfwk50, Sum([wk 51]) AS SumOfwk51, Sum([wk 52]) AS SumOfwk52 "
strSQL_Consol = strSQL_Consol & "FROM tblConsol WHERE Week = '4 Week Average' AND ([wk 4]+[wk 5]+[wk 6]+[wk 7]+[wk 8]+[wk 9]+[wk 10]+[wk 11]"
strSQL_Consol = strSQL_Consol & "+[wk 12]+[wk 13]+[wk 14]+[wk 15]+[wk 16]+[wk 17]+[wk 18]+[wk 19]+[wk 20]+[wk 21]+[wk 22]+[wk 23]+[wk 24]"
strSQL_Consol = strSQL_Consol & "+[wk 25]+[wk 26]+[wk 27]+[wk 28]+[wk 29]+[wk 30]+[wk 31]+[wk 32]+[wk 33]+[wk 34]+[wk 35]+[wk 36]+[wk 37]"
strSQL_Consol = strSQL_Consol & "+[wk 38]+[wk 39]+[wk 40]+[wk 41]+[wk 42]+[wk 43]+[wk 44]+[wk 45]+[wk 46]+[wk 47]+[wk 48]+[wk 49]+[wk 50]"
strSQL_Consol = strSQL_Consol & "+[wk 51]+[wk 52]) <> 0 "
strSQL_Consol = strSQL_Consol & "GROUP BY RefCode"
Thanks..
is there any other way to simply the WHERE clause.. from wk 4 to wk 52 should not be zero(0)..
strSQL_Consol = "SELECT RefCode, "
strSQL_Consol = strSQL_Consol & "Sum([wk 1]) AS SumOfwk1, Sum([wk 2]) AS SumOfwk2, Sum([wk 3]) AS SumOfwk3, Sum([wk 4]) AS SumOfwk4, "
strSQL_Consol = strSQL_Consol & "Sum([wk 5]) AS SumOfwk5, Sum([wk 6]) AS SumOfwk6, Sum([wk 7]) AS SumOfwk7, Sum([wk 8]) AS SumOfwk8, "
strSQL_Consol = strSQL_Consol & "Sum([wk 9]) AS SumOfwk9, Sum([wk 10]) AS SumOfwk10, Sum([wk 11]) AS SumOfwk11, Sum([wk 12]) AS SumOfwk12, "
strSQL_Consol = strSQL_Consol & "Sum([wk 13]) AS SumOfwk13, Sum([wk 14]) AS SumOfwk14, Sum([wk 15]) AS SumOfwk15, Sum([wk 16]) AS SumOfwk16, "
strSQL_Consol = strSQL_Consol & "Sum([wk 17]) AS SumOfwk17, Sum([wk 18]) AS SumOfwk18, Sum([wk 19]) AS SumOfwk19, Sum([wk 20]) AS SumOfwk20, "
strSQL_Consol = strSQL_Consol & "Sum([wk 21]) AS SumOfwk21, Sum([wk 22]) AS SumOfwk22, Sum([wk 23]) AS SumOfwk23, Sum([wk 24]) AS SumOfwk24, "
strSQL_Consol = strSQL_Consol & "Sum([wk 25]) AS SumOfwk25, Sum([wk 26]) AS SumOfwk26, Sum([wk 27]) AS SumOfwk27, Sum([wk 28]) AS SumOfwk28, "
strSQL_Consol = strSQL_Consol & "Sum([wk 29]) AS SumOfwk29, Sum([wk 30]) AS SumOfwk30, Sum([wk 31]) AS SumOfwk31, Sum([wk 32]) AS SumOfwk32, "
strSQL_Consol = strSQL_Consol & "Sum([wk 33]) AS SumOfwk33, Sum([wk 34]) AS SumOfwk34, Sum([wk 35]) AS SumOfwk35, Sum([wk 36]) AS SumOfwk36, "
strSQL_Consol = strSQL_Consol & "Sum([wk 37]) AS SumOfwk37, Sum([wk 38]) AS SumOfwk38, Sum([wk 39]) AS SumOfwk39, Sum([wk 40]) AS SumOfwk40, "
strSQL_Consol = strSQL_Consol & "Sum([wk 41]) AS SumOfwk41, Sum([wk 42]) AS SumOfwk42, Sum([wk 43]) AS SumOfwk43, Sum([wk 44]) AS SumOfwk44, "
strSQL_Consol = strSQL_Consol & "Sum([wk 45]) AS SumOfwk45, Sum([wk 46]) AS SumOfwk46, Sum([wk 47]) AS SumOfwk47, Sum([wk 48]) AS SumOfwk48, "
strSQL_Consol = strSQL_Consol & "Sum([wk 49]) AS SumOfwk49, Sum([wk 50]) AS SumOfwk50, Sum([wk 51]) AS SumOfwk51, Sum([wk 52]) AS SumOfwk52 "
strSQL_Consol = strSQL_Consol & "FROM tblConsol WHERE Week = '4 Week Average' AND ([wk 4]+[wk 5]+[wk 6]+[wk 7]+[wk 8]+[wk 9]+[wk 10]+[wk 11]"
strSQL_Consol = strSQL_Consol & "+[wk 12]+[wk 13]+[wk 14]+[wk 15]+[wk 16]+[wk 17]+[wk 18]+[wk 19]+[wk 20]+[wk 21]+[wk 22]+[wk 23]+[wk 24]"
strSQL_Consol = strSQL_Consol & "+[wk 25]+[wk 26]+[wk 27]+[wk 28]+[wk 29]+[wk 30]+[wk 31]+[wk 32]+[wk 33]+[wk 34]+[wk 35]+[wk 36]+[wk 37]"
strSQL_Consol = strSQL_Consol & "+[wk 38]+[wk 39]+[wk 40]+[wk 41]+[wk 42]+[wk 43]+[wk 44]+[wk 45]+[wk 46]+[wk 47]+[wk 48]+[wk 49]+[wk 50]"
strSQL_Consol = strSQL_Consol & "+[wk 51]+[wk 52]) <> 0 "
strSQL_Consol = strSQL_Consol & "GROUP BY RefCode"
Thanks..