get Max larger than zero

  • Thread starter Thread starter ten
  • Start date Start date
T

ten

I have some tables, where table B is a sub of the table
A. Table A contains a lot of properties, where Table B
contains sub-properties, and has fields for e.g. area and
building year. The latter comntains either 0 (not filled)
or a year.

What I want is to get the sum of the area and the
smallest building year (if several) from table B for each
property in table A. I know I can use the Sum-function
for getting the area (that works) but if I use the Min-
function for year I will get zero-values if there are
some, and I do not want that!
And if I exclude values for year = 0, I loose a lot of
area-information (se example)

For instance
Table A contains
ID_1 Property_1
ID_2 Property_2

Table B contains (The ID_1 and ID_2 links to table A)
ID_1 100_m2 1990
ID_1 60_m2 1987
ID_1 10_m2 0
ID_2 50_m2 1957
ID_2 10_m2 0

The result I want is like this
Property_1 Area-sum=170 Year=1987
Property_2 Area-sum=60 Year=1957

Appreciate any suggestions! Thanks!
 
This trick should work for you, use the MAX function on
the negative years instead: SUM(), -MAX(-Year)
Jerry B.
 
If I am not doing anything wroong, this still gives the
wrong year: The lowest of {-1990, -1987, -0} are -1990,
so your suggestion -MIN(-Years) gives 1990, when what I
really want is 1987.
 
You are right, I missed the fact that the one you need is
in between 0 and whatever... I guess you will need to use
a combination of MIN and IIF inside, something like:
 
Thanx!
That seems to have done the trick ;-)
-----Original Message-----
You are right, I missed the fact that the one you need is
in between 0 and whatever... I guess you will need to use
a combination of MIN and IIF inside, something like:

.
Click to expand...
 
Back
Top