Querying only x number of records

[Mike P]

How do I write a query to only pull back the most recent x number of records?
For example, sometimes I wish to pull back the most current 4 records, the
most current 22 records, and all the records. I know who to do this in a
program using a loop with a counter and a NextRecord.

Anyway to do this in a query without writing code and using a single query
with a [GetNumberOfRecords] parameter???

 

[Dale Fye]

Mike,

Don't know of any way to do do this with a variable number of records,
without writing a little code. But the code would be extremely simple.

Lets assume you have a textbox (txt_ReturnRecords) where you want to enter
the number of records to return. Lets also assume you have a command button
which will actually requery the forms record source based on this value.
Lastly, lets also assume that you have a date/time field (LastUpdated) which
is how you are going to determine the most recent records. This field should
have a Default Value set in the database table as: =Now()

Then, in the command buttons Click event, you would add some code like:

Private sub cmd_Requery_Click

Dim strSQL as string

strSQL = "SELECT TOP " & me.txt_ReturnRecords " _
& "Field1, Field2, Field3, ... " _
& "FROM yourTablename " _
& "ORDER BY LastUpdated DESC"
me.RecordSource = strSQL

End Sub

If you are using a Date/Time field which uses =Now() as the default value in
your your LastUpdate field, you will probably not get too many duplicates
(unless you do an insert into the table that contains a large number of
records all at once). However, you should keep in mind that the TOP clause
of the SELECT statement will return how ever many records you indicate, plus
those where the Sort Criteria matches that number. To explain this better,
if you are sorting by a numeric field that contains values 1, 2, 3, 3, 4, 4,
4, 5, 5, 5, 6, 7, 8, 9 and you ask for the TOP 3 sorted ascending, you will
actually get 4 records (1, 2, 3, 3). If you ask for the TOP 5, you will
actually get 7 (1, 2, 3, 3, 4, 4, 5).

HTH
Dale

 

[Michel Walsh]

Indeed, with Jet, building the query statement at runtime seems the fastest
way, in execution time.

With MS SQL Server 2005, you can use: SELECT TOP (@param) ... NOTE: you
need the parenthesis around the parameter name, in that case, though. That
syntax is not supported by Jet 4..



Vanderghast, Access MVP

Mike,

Don't know of any way to do do this with a variable number of records,
without writing a little code. But the code would be extremely simple.

Lets assume you have a textbox (txt_ReturnRecords) where you want to enter
the number of records to return. Lets also assume you have a command
button
which will actually requery the forms record source based on this value.
Lastly, lets also assume that you have a date/time field (LastUpdated)
which
is how you are going to determine the most recent records. This field
should
have a Default Value set in the database table as: =Now()

Then, in the command buttons Click event, you would add some code like:

Private sub cmd_Requery_Click

Dim strSQL as string

strSQL = "SELECT TOP " & me.txt_ReturnRecords " _
& "Field1, Field2, Field3, ... " _
& "FROM yourTablename " _
& "ORDER BY LastUpdated DESC"
me.RecordSource = strSQL

End Sub

If you are using a Date/Time field which uses =Now() as the default value
in
your your LastUpdate field, you will probably not get too many duplicates
(unless you do an insert into the table that contains a large number of
records all at once). However, you should keep in mind that the TOP
clause
of the SELECT statement will return how ever many records you indicate,
plus
those where the Sort Criteria matches that number. To explain this
better,
if you are sorting by a numeric field that contains values 1, 2, 3, 3, 4,
4,
4, 5, 5, 5, 6, 7, 8, 9 and you ask for the TOP 3 sorted ascending, you
will
actually get 4 records (1, 2, 3, 3). If you ask for the TOP 5, you will
actually get 7 (1, 2, 3, 3, 4, 4, 5).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

How do I write a query to only pull back the most recent x number of
records?
For example, sometimes I wish to pull back the most current 4 records,
the
most current 22 records, and all the records. I know who to do this in a
program using a loop with a counter and a NextRecord.

Anyway to do this in a query without writing code and using a single
query
with a [GetNumberOfRecords] parameter???