maximum values

[sgold72]

I am trying to build a query that will show the three maximum and minimum values of a field for each group of records. For example, I have 100 records that belong to 5 groups, for each group, I want to only show the records that contain the greatest and least values of a field.

I am not at all knowledgable of VBA or SQL, and I have a feeling my ability with Access might have come to an end without understanding programming. I hope I am wrong though!!

Thanks..
Sandy

 

[Nouba]

you can achieve this by using a query which restricts the values by defining two subqueries used in the where clause with the appropriate order direction.

SELECT
  T1.aGroup
, T1.aValue AS TopBtmVal
FROM aTable AS T1
WHERE T1.aValue In
  (Select TOP 3
    aValue
  From aTable
  Where aGroup=T1.aGroup
  Order By aValue Desc)
Or aValue In
  (Select TOP 3
    aValue
  From aTable
  Where aGroup=T1.aGroup
  Order By aValue)
ORDER BY
  T1.aGroup
, T1.aValue DESC

 

[sgold72]

thank you so much for your help!

i have been reading some access programming books from the library and getting familar with the language - but very confused at the same time. it seems everything i look at uses different language rules.

i copied your code into the sql view of a new query, replacing all the aitems with the proper names in my db. however, i didn't get it to work. some syntax error - the computer puts a million parantheses in the code

is it easier if i just do the max values, and do the min values in another query?

thank you - i am extremely grateful for your time

Sandy

 

[Nouba]

here is a sample of the above query.