SPECIFIC NO OF RECORDS

  • Thread starter Thread starter smita
  • Start date Start date
S

smita

I want a query (or filter)the retrieves specific no of records ((10,15,20
etc)from a table.
 
I want a query (or filter)the retrieves specific no of records ((10,15,20
etc)from a table.
Click to expand...

Create a query; view its Properties; and set the "Top Values" property to the
desired number.
 
Thanks john!
i already tried this. it will retrive the records that have tpomost five
values!
i tried 'Max Records' property. that is also not working or i am not using
properly? can u help with this?
 
Perhaps you would care to share the query you have used and explain why
the data that is being returned does not meet your specification.

If there are duplicates for the last position then all the ties will be
returned. You can avoid the duplicate problem by adding the primary key
of the tables involved to the sort.

SELECT TOP 5 FieldA, FieldB
FROM SomeTable
ORDER BY SomeTable.FieldA, SomeTable.PrimaryKeyField

That will give you exactly 5 records since the PrimaryKey field is
UNIQUE and therefore the TOP 5 will be unique.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks john!
i already tried this. it will retrive the records that have tpomost five
values!
i tried 'Max Records' property. that is also not working or i am not using
properly? can u help with this?
Click to expand...

Step back a bit an tell us something about the context. What exactly are you
trying to accomplish? What's the structure of your table? Do you care WHICH
five records, or just any five?
 
Thanks!
Actually i am developing this for a badminton court where i want 4 players
who are going to play the next match depending on entry in the court and no
of matches played. my table main fields are for player,status(playing or
sitting), no of matches played. whoever has played less matches will be
eligible for the next match.

Now if i set top values property to 4 it will return duplicate records
also. please help. any use of max records property?
 
Thanks!
Actually i am developing this for a badminton court where i want 4 players
who are going to play the next match depending on entry in the court and no
of matches played. my table main fields are for player,status(playing or
sitting), no of matches played. whoever has played less matches will be
eligible for the next match.

Now if i set top values property to 4 it will return duplicate records
also. please help. any use of max records property?
Click to expand...

If you just want a random selection - say there are eleven eligible players -
you could add a calculated field to the query to make the final cut. Put this
little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see (4 in this case).
 
Back
Top