Can you access the database
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).:think:
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
