Tags: access, database, details, following, item, max, microsoft, mysql, okabc, oracle, query, sql, statusabc, strange

using last , max from access query

On Database » Microsoft Access

2,850 words with 2 Comments; publish: Tue, 03 Jun 2008 22:23:00 GMT; (25078.13, « »)

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

All Comments

Leave a comment...

  • 2 Comments
    • 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)

      \\// Live Long and Prosper \\//

      BS"D

      "maxzsim" wrote:

      > 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

      #1; Tue, 03 Jun 2008 22:24:00 GMT
    • i c ..

      tks for ur reply

      "Ofer" wrote:

      [vbcol=seagreen]

      > 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)

      > --

      > \\// Live Long and Prosper \\//

      > BS"D

      >

      > "maxzsim" wrote:

      #2; Tue, 03 Jun 2008 22:25:00 GMT