Preventing calculated field from exceeding value in another column 1

[Sashanan]

One of my Access 2000 queries includes a calculated field named Target, based on several columns in the queried tables. The calculation works fine as it is, however I want to compare the end result to another column in the same query, and if it exceeds the value in this column, I want to equalize this. Right now it's something like:

Target: round(Column1 * Column2 / Column3 + Column4)

I want to compare the result of this to Column5, and then equalize it if it exceeds Column5. Something like "If Target > Column5 then Target = Column5", but then in SQL lingo.

Not sure how to go about this. Is there an SQL solution for this - comparing the values of two columns and taking the highest of the two, for instance - or will I need to write a VBA function for this? If so, what would it look like?

Thanks in advance!
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[indyaries]

Sashanan,

Not a complete answer, but it may get you on track.
I had an occassion where I needed to compare the values in two fields to see if they were equal. The expression would then display "Increase" or "Decrease" or "Equal". Anything not "Equal" was an "error". Here is the expression I used in a query. It creates a new field, and indicates "Increase" or "Decrease" or "Equal" depending on the results of the comparison:

Results: IIf([WC4A]>[WC5A],&quot;Increase&quot;,IIf([WC4A]<[WC5A],&quot;Decrease&quot;,&quot;Equal&quot)

The below expression will duplicate the results of Column5. Perhaps you can then use an Update Query to make the changes?:

NewTarget: IIf([Target]>[Column5],[Column5],&quot;Equal&quot

HTH
Bob

 

[Sashanan]

Turns out I need to do things a little differently than I thought (started a new thread detailing my current problem), but I'll certainly try this out if I come across a situation where I do need to perform this kind of calculation. Thanks for the insight!
&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)