using last , max from access query

[Guest]

Hi,
how does the last , max actually works in access query ?

using the following details i got "strange results"

item year month status
abc 2005 12 ok
abc 2005 11 ok
abd 2006 01 ok
abd 2004 03 ok

if i uses "select item , max(year) , max(month) from tbl where status =
"ok" "
it returns me

abc , 2005 , 12
abc , 2006 , 03

it seems that the max only worksindependently on each column and it the
month does not seem to depend on the max(year) return for the year

is this how max , first or last shld be working ?

i am very confused (and frustrated as well , in the end i have to do several
sub queries to acheive the results that i wanted)

appreciate ur advise

tks & rdgs

 

[Guest]

Yes. each of the Last, Max, Min works on the column itself only and not the
all record, and I admit that it confused alot of people that I worked with
that thought that all the record is returned.
To return the all record you'll need to use the above as the filter for the
query

Select * From TableName Where FieldName = Dmax("FieldName","TableName")

Or
Select * From TableName Where FieldName In (Select Max(FieldName) From
TableName)

 

[Guest]

i c ..
tks for ur reply

Yes. each of the Last, Max, Min works on the column itself only and not the
all record, and I admit that it confused alot of people that I worked with
that thought that all the record is returned.
To return the all record you'll need to use the above as the filter for the
query

Select * From TableName Where FieldName = Dmax("FieldName","TableName")

Or
Select * From TableName Where FieldName In (Select Max(FieldName) From
TableName)