I've had this database for many years now. It works well, however there is one thing I have not every been able to figure out. How to update a field based on another, I was hoping for Automatically but think that might be tricky.
I know an Update Query will do the job but the catch is, the update varies depending on the other field. The fields in questions, well call them A and B. the Update is if B = some value then A should update to a certain value but only if A is Null.
If Field B = 1 then Field A should update to 11
if Field B = 2 then field A should update to 12
if field B = 3 then field A should update to 13 etc etc.
The numbers I am actually using are longer but the idea is the same. In an actual Update query you can only do 1 value at a time, at least that I know how to do.
There is also another issue, there is one combination that if B = 5, and field C = a certain number, then A should be 15, but if C = something else, then A should be 155.
Complex I know, but I am hoping someone can help me do this.
Even if a SQL statment is used and it is explained how to write it that woudl be great.
Is there a way to do this, and have all the values i need listed so that they all update?