The normalisation part is easy, the SQL is tricky.
Normalising you will end up with two (possibly three) tables. The first table is the parent table with a primary unique key of "ID". Put any columns in this table for data attributes that pertain to, and only to, the ID. Perhaps the ID is a person so the additional columns will be things like Name, Sex, and so on.
Now we have to consider the scores. There is an implied significance by order here that means there are really two data items - a score number and the score itself. This is where you may choose to create a second parent table for score numbers. Perhaps there is additional data that can be added to the score number table; for example each number may refer to a different test such as Reading, Writing, Arithmetic, and so on.
The child table holds the scores themselves. It has as a minimum three columns: ID being the foreign key of the parent ID table; Score Number that is the foreign key of the second parent table if you have implemented it or simply a score number; the score itself. The combined key of ID & Score Number should be unique.
OK now for the SQL and this is where things get messy. If you only want one of your summaries at a time things are not too bad and you need to write a different SQL query for each total. However I suspect you want them all at the same time and the only way I can think of doing this is by using nested IIF expressions. I'm not sure how deep you can nest these - some one out there will tell us - and I'm not convinced that there are not more elegant solutions - again some one will tell us. So here is some sample SQL using as near as possible the example you give.
SELECT ID, IIf([ScoreNum]<=3,"Score_1_3",IIf([ScoreNum]<=6,"Score_4_6","All others") ) AS TotalType, Sum(Score) AS TotalScore
GROUP BY ID, IIf([ScoreNum]<=3,"Score_1_3",IIf([ScoreNum]<=6,"Score_4_6","All others") ) ;
The Table in the above SQL is the child table. Things get really complex when you add joins and deeper IIF nesting. If I think of anything more elegant myself I will let you know.
It seems to me that Normalization is good for data structure and memory, however, it becomes quite complicated even just to calculate simple totals, I actually other calculations which is more complex. For example, if Score_1_3 is between 0 and 10, Score_1_3_Value is 1 and so on. Denormalization really consumes memory however the query is really simple. Is this true ?
You have probably hit on a basic truth. Normalisation is good for data, data structures, data relationships, data validation, and is efficient in the respect that is minimises space. However as you rightly point out sometimes the extraction and analysis of normalised data can be a 'bitch.'
I personally appreciate the normalisation process. It helps me understand the data and relationships, and identify any missing information. There is always a denormalisation process before you can achieve a practical working model and, if you start from a fully normalised model, you can identify what features and facilities are being abandoned. For example you may go back to your client and tell him that this solution will only work for a single organization (because you have eliminated all the structures supporting a multi organization) or that he can only have opening balances each month (because you have collapsed and denormalised the month structure into the account table).
OK, back on topic. Your new requirement is quite simple to achieve. Again it uses the Iif statement. Once you have calculated your totals then use something like the following: