integrating a comparison within a query

[Stan]

I have a database consisting of several tables that contain information on
patients. Each unique patient has information linked across tables by their
ID number.

I have a query that returns two columns of numerical data (call them A and
B) from two separate tables, for each patient. I would like this query to
also return the maximum value when comparing A and B. For example, the column
"max value" would return the value for A if A >= B , and would return the
value for B if B >= A . I am not sure how to get this using the design view -
any help would be appreciated. I imagine there is any easy way to do simple
operations/comparisons within an access query but I can't find info on this
in the help sections. thanks!

 

[Stan]

I am able to get a mostly functional result with
maxscore: IIf(__, __, __)

but this does not work in cases where there is a null value. If data are
available only from one column, that column's value should be returned. If no
data are available I want the query to return the null value. any ideas?

 

[John Spencer]

Are the values always positive?

IIF(Nz(A,0)>Nz(B,0),A,B)

Or a bit more complex (and untested)

IIF(Nz(A,B)>Nz(B,A), Nz(A,B), Nz(B,A))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Stan]

yes the values are always positive and between 0-5 if not null.
that first formula didn't quite get all the null values - it works if B is
null (to return A) but does not return A if B is null.

i tried this which works although is a little klunkier
max: IIf(A>=B Or IsNull(B),A,B)

thanks.