I am retrieving top 5 data from the access table. I tried it with date and it is sorting according to the date and displaying the data. When I tried to sort the same query using a numeric column it is showing all the records according to the conditional statement (where clause).

In my program I need to sort it using the numeric value column only (For example according to the importance - I am assigning the importance by number varying from 1-5 and I want to take the top 5 data which is sorted by the importance - the table will contain a lot of data with varying importance).

I tried with two column sort order also and it displays all the records in the table.

SK

__________________
Thanks in advance.

Regards,

Senthil Kumar M.

Well, if you take the top five values of a field that contains only the values 1 to 5, my thinking is that that means EVERY record since there are only 5 values in 1 to 5.

Maybe what you want is the FIRST 5 RECORDS rather than the TOP 5 VALUES.

Was this question a test to see if we are paying attention to your specification? :D

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Yes I am taking the top (first) 5 records from the database but I am sorting it by using the importance which is the integer value. If there is some 3 records for importance 1 and 5 records for importance 2, I want to display the 3 records of importance 1 and 2 records of importance 2 (totally 5).

SK

I understand what you're going for. But "top 5" has a different meaning than "first 5". The top 5 "values" means find the top 5 values of a field, then provide all the records that have one of those 5 values in that field. If the field only contains 5 distinct values, as in the range 1 to 5, there are only 5 values. So you will get every record.

If you had 1=1, 15=2, 2=3, 1=4, 1=5 then you will display 20 records if you take the top 5 values.

If you had 1=1, 1=2, 1=3, 1=4, 1=5, 10=6, then you will get 5 records if you display the top 5 values.

If you had 1=1, 1=2, 1=3, 1=4, 21=6, then you'll get 25 if you display the top 5 values. 21 one of those records will have the value 6.

You're going to have to find another way to qualify getting the "top 5". For instance, in your example, how is Access supposed to know which 2 out of the 5 records with importance=2 are supposed to be provided? When you figure that out, then what you're really going after is the first 5 records based on your sort. Unfortunately, unless your data is coming from ODBC or SQL, you can't limit to 5 records (MaxRecords).

Here's a link at MSDN that tells more: http://msdn.microsoft.com/library/de...oTopValues.asp

What I need to understand is how you're retrieving the data and what are the circumstances for which you are limiting to only 5 - for a report/form or simply within your program? If within your program, go ahead and retrieve all of the data and count as you pull off the first 5.

Or is there some reason that you think you have limit the data to the first 5 records? Are you worried about hogging memory?

Actually I am displaying some reports according to various categories like some 5 Important, 5 General and some 5 Other categories. In General category, there is no such type of sorting but is sorted according to the timespan.

But in Important and Other Categories are sorted to the importance assigned to it. There only I am getting the problems and I am displaying it using a repeater control. Ok. I will try it out using a static variable according to your idea.

SK