Derived Column with SQL equation and IIF

[rivereridanus]

Hello,

I have a table called 'survey' which has three columns (amongst others) called q9, q9unitoftime, and q9numsperyear. Q9numsperyear is a derived column from the other two. I wrote an update query to make this work.

UPDATE survey SET q9numsperyear=


(Iif(q9unitoftime=1, q9*52, Iif(q9unitoftime=2, q9*12, Iif(q9unitoftime=3, q9))));

This is good for a one time update, but I want access to perform that calculation on its own once q9 and q9unitoftime have been entered through a form.

Can I do something like this on an event or something?

[rpeare]

The simple answer is do not store calculated values on your table. There is no reason to do it from the example you've provided. When you start reporting or doing data analysis through queries then you can perform the calculation. I'd really strongly recommend you not store the value you're after.

If you are absolutely adamantly going to do this regardless of good practice you can use that same formula if you are using a bound form you would just set the ON EXIT event of your two calculation fields to the same thing

q9numsperyear=
(Iif(q9unitoftime=1, q9*52, Iif(q9unitoftime=2, q9*12, Iif(q9unitoftime=3, q9))));

Where SQnumsperyear is the name of the bound control on your data entry form.

[rivereridanus]

Yeah, I know you're not supposed to store derived values. I thought about that when I was doing it. The reason that I did was because I needed to get the average of all those values, and I couldn't think of how do AVG(something) without them being stored somewhere. If there is a better way to do that, I will definitely not store them anymore.

Thanks for your help!

[rpeare]

if you're putting the data on a report you can average items on a group or over the entire data set you'd just have a formula in a text box in your group or report footer that was

=sum([fieldname])/count([fieldname])

where fieldname would be a numeric field