Returning max values that are not NULL

[osmondB]

Hi all,

I'm having problems with a query I'm trying to create. I
have a table that looks like this:

fieldOne | fieldTwo
____________________
A | 1
A | 2
A | 3
B | 4
B | 5
B | 6
C | 7
C |
C |

The last two records for C have no values for fieldTwo.
What I want my query to return are the records for A,B, and
C that have the highest value in fieldTwo which is not
null. So the query for the above table should return:

fieldOne | fieldTwo
____________________
A | 3
B | 6
C | 7

I've tried using writing something along the lines of

=Max("fieldTwo") And Is Not Null

in the criteria field of fieldTwo but I keep getting errors
about aggregate functions in a WHERE clause? I have no
knowledge of VB and extremely little knowledge of SQL so I
am actually creating this query in Design View. If any of
you could help at all it would be greatly appreciated!
Thanks a mil!

osmondB

 

[Jeff Boyce]

If you will open a query in design mode, select the Totals type query, use
the GroupBy aggregate function on the FieldOne, and Max aggregate function
on FieldTwo, and use a selection criteria of Is Not Null (on FieldTwo), I
believe you can get what you are after.

 

[osmondB]

That worked perfect! Thanks a bunch Jeff

osmondB

 

[Genius Maximus]

Try the NZ () function:

= max (nz([fieldTwo];0)

success

 

[Michel Walsh]

Hi,


In theory, MAX( ) does not consider NULL, so, as long as there is one
record, in each group, with a not-null value, in consequence, a simple:


SELECT f1, MAX(f2)
FROM myTable
GROUP BY f1



should be all you need. You would get a NULL only if all records, for a
given value of f1, have their f2 value NULL.



Hoping it may help,
Vanderghast, Access MVP