On Being too Clever

A current project I am working on (ASP.net with an Access Database Backend) needed a new feature implemented that allows users to see a report of non-compliance observations.  I was trying to write up a clever query that allowed me to see a count of non-compliance observations for each service but was having a difficult time finding a solution to this problem.  I was determined to write up a query that used aggregates to get the data and happily display it to the user. But I ran into some problems and spent the last few hours trying to find a solution to this.  I looked at Pivot tables, crosstab queries and all sort of things without finding my solution.  Finally I stepped back and thought that this was just way too complicated for a solution that shouldn’t be this complicated.

The eventual solution was to just store these non-compliant observations on their own table (with the appropriate look-up fields included).  I then created a simple query that gave me the information I wanted!  Solution found in just a few minutes!  So the moral of this story is to not always use (or find) the most clever solution to the problem you are trying to solve.   My original thought was that I didn’t want to duplicate data somewhere else on the database, but in this case the duplication of the data was justified.  I am not anticipating this new table to be heavily utilized.  In fact, analyzing some of the recent data that has been entered by the users found that out of 48 observations entered, only 1 would have been entered into this new table.  So I am sacrificing a bit of data storage (and maybe a small bit of efficiency) but in the end the solution I found is rock solid and easy to extend if needed for future use.