Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / February 2008

Tip: Looking for answers? Try searching our database.

Querying only x number of records

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike P - 22 Feb 2008 19:27 GMT
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 - 22 Feb 2008 20:51 GMT
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
Signature

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
[quoted text clipped - 3 lines]
> Anyway to do this in a query without  writing code and using a single query
> with a [GetNumberOfRecords] parameter???
Michel Walsh - 25 Feb 2008 14:25 GMT
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,
>
[quoted text clipped - 55 lines]
>> query
>> with a [GetNumberOfRecords] parameter???
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.