Simplify SQL string

[gnod]

Hi,

is there any other way to simply the WHERE clause.. from wk 4 to wk 52 should not be zero(0)..

[vba]
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"
[/vba]

Thanks..

[ammx]

Hi gnod,

your DB design is killing you....

...if you have chance to maintain the database directly I would consider
a. redesign your database (most effective in the long run, but extra work to start with)
b. add an SQL-VIEW to precalculate some stuff (keeps existing DB design, easy to implement)

a. Instead of having multiple fields like [wk 1], [wk 2] etc. you should better have a child table (tblWeek) to the tblConsole that uses the same fields as the primary unique key of tblConsole + the fields WeekNr and WeekValue. The WeekNr is 1, 2, ..., 53, the WeekValue is the value that you stored in [WK 1], ...[WK 52] before. Keep in mind that some years may have 53 weeks, but your last field is [WK 52]. In the tblWeek this is not a problem.
The primary unique key of tblWeek is the same PUK as of tblConsole + WeekNr. It makes it easier to maintain and retrieve data, but in your case you also have to change the application that fills the tblConsole (and any other program that uses tblConsole). In the theory, this is called 'database normalization' and should be done during the DB design phase before developing lot's of code.
(Browse the internet for 'database normalization' or 'third normal form' to learn more).

b. if you cannot change the DB design, you may try to add an SQL-VIEW, also called QUERY (ie. vwConsole) to the DB. In the view you can precalculate [WK 1] + [WK 2] + ... [WK 53] and called it ie. WkChecker. You can also prebuild the sums SumOfwk1.. SumOfwk52. It also contains the fields RefCode and Week from tblConsole. Then your query becomes something like:

SELECT RefCode, SumOfwk1, SumOfwk2, ...,SumOfwk52
FROM vwConsole WHERE Week = '4 Week Average' AND
WkChecker <> 0

You could use MS ACCESS to use the query wizard to build the right formulas and then copy the SQL statement created to your DB.

Hope this helps a little bit.

Greets,

Michael

[gnod]

hi,

tblConsol is a storage from all worksheets that i want to consolidate..

the sql that i want to simply is a query from tblConsol.. that's why it has a SUM function so i can get the total for wk 1, wk 2, ... wk 52..

do you think i still need to add another query to avoid the ([wk 1] + [wk 2] ...[wk 52] <> 0) in the WHERE clause?

i attach the MDB file for your reference..

[geekgirlau]

Go with Michael's suggestion - you really need to normalise the database structure before you do anything.

How are you consolidating the data from your spreadsheets into your database? There may be an option whereby you can import the data using its current structure, then append from there to a normalised table structure.

[Bob Phillips]

As you seemed determine to ignore the advice of changing the flat file to a database, why not just import it all into Excel, and do your an alysis there?

[gnod]

How are you consolidating the data from your spreadsheets into your database?

i get the data from several workbooks exported to MDB then i create a query to sum from wk 1 to wk 52..

ok, i'll study how the database normalization..

Thanks..