How do I put the results of an SQL in a final field

[andy49]

Hope someone can help. I'm very new to access. I'm using Access 2010



I have two tables Results and levels

Members of my team input three numbers into the results table for each student in a class they teach (presented by a query in datasheet view) and the table calculates a raw score. I've written an SQL which calculates the level at which the students are working. This varies upon which test paper they took and what total score they got.



But I need this level to be put back into the results table in a final column and that's where I'm stuck.

Hope someone can help.

[JoeM]

But I need this level to be put back into the results table in a final column and that's where I'm stuck.

Why?

The general rule of thumb is this: Usually, anything that can be calculated should NOT be stored in a table field. If it can be calculated at any time, there is usually no reason to store it in a table field. A query can be used for all the same things a table can (Data Source of Forms, Reports, and Exports).

So is there some reason why you think you need to store it at the table level?

[andy49]

Why?

The general rule of thumb is this: Usually, anything that can be calculated should NOT be stored in a table field. If it can be calculated at any time, there is usually no reason to store it in a table field. A query can be used for all the same things a table can (Data Source of Forms, Reports, and Exports).

So is there some reason why you think you need to store it at the table level?

Thanks for the prompt reply Joe

Another important noob lesson learned

[JoeM]

Glad to help!

It is a common thing, maybe noobs think that they need to store calculated values in table fields, when actually you don't want to. Doing so usually violates the Rules of Normalization and can undermine the integrity of your data.

For example, Let's say you have a simple calculation adding FieldA to FieldB. Let's say that you decided to store the value in a table field named FieldC. Now, let's say at some point down the road, the value in FieldA for that record changes. Unless you do something to recalculate and update the value in FieldC, FieldC will no longer be equal to the sum of the values in FieldA and FieldB! That can present problems! However, if you just leave it as a calculated field in a query, any changes to FieldA or FieldB will automatically be reflected in the calculated field.

If you are familiar with Excel, thinking of it as whether you have an Excel formula, or change the formula to its hard-coded value result. Leaving it as a formula makes the answer dynamic, and accounts for any changes in the underlying factors.

[andy49]

Being much more confident with excel, that makes perfect sense.