Solved

Retrieve most current records

Posted on 2011-03-22
3
226 Views
Last Modified: 2012-05-11
I currently have a very simple Access query that retrieves all records within a date range.
I frequently have to run it multiple times to wind up with the approximate number of records I'm looking for in a given situation.

As an example, let's say on a given day, I'm interested in retrieving about 500 records.

What I currently do is just guesstimate from experience that within a 1 month period, there will be about 500 records returned.  So, I set up the query to retrieve, say November 1, 2010 through November 30, 2010 data and see how many records get returned.  What frequently happens is too few records may be returned or too many records (depending on what date range I use). Using the November 1 thru November 30 example above, let's say the query returns only 200 records. What I wind up then doing is rerunning the query with an expanded date range (say October 1, 2010 thru November 30, 2010) to see if that gets me closer to 500 records. Etc Etc.

What would be ideal (and that I of course don't know how to do; that's why I'm asking) is to be able to just query a wide date range where I'm almost always guaranteed to be returned at least as many records as I'm looking for.  Within that same query, then retrieve the MOST CURRENT, say 500 records from whatever records were returned.

In other words, let's say I query a wide date range such as 1/1/20 thru 12/31/10.  Let's say that query returns something huge such as 100,000 records.  I don't want all 100,000 records. I just want the most current 500. By most current I mean most current datewise (My table has a date field).   Any easy way to do this?
0
Comment
Question by:dbfromnewjersey
3 Comments
 
LVL 4

Accepted Solution

by:
philetaylor earned 125 total points
ID: 35193412
Something like:

SELECT TOP 500 * from tablename ORDER BY datefield DESC

this will return the last 500 records. If you want to only return records before a particular date, you could do:

SELECT TOP 500 * from tablename WHERE datefield < #2010-01-01# ORDER BY datefield DESC

You can replace the * with a list of fields (fielda,fieldb,fieldc etc)

Cheers

Phil
0
 
LVL 12

Assisted Solution

by:telyni19
telyni19 earned 125 total points
ID: 35193624
You can specify in your query a certain fixed number of records to return, as Phil described, but you can't directly set the number as a variable or parameter. In order to choose how many records to return without modifying the query directly, you would need to have a form with an input field and have the query programmatically generated through SQL. See attached for an example of how to do that.

The dates to filter the results by could also be added programmatically from input fields on the form, making the query even more versatile.
Private Sub Command2_Click()
Dim strsql As String
Dim dbCur As Database
Dim qryTop As QueryDef
Dim intRecs As Integer

Set dbCur = CurrentDb

intRecs = Int(Nz(Me.txtRecCount.Value, 0))

If intRecs > 0 Then
strsql = "SELECT TOP " & intRecs & _
" TableData.ID, TableData.NumberVal, TableData.CreationDate" & _
" FROM TableData WHERE (((TableData.CreationDate) Between #1/1/2011#" & _
" And #2/28/2011#)) ORDER BY TableData.CreationDate DESC;"

Set qryTop = dbCur.QueryDefs("TopRecordsbyDate")

qryTop.SQL = strsql
DoCmd.OpenQuery "TopRecordsbyDate"
End If

Set qryTop = Nothing
Set dbCur = Nothing
End Sub

Open in new window

TopCountExample.zip
0
 

Author Comment

by:dbfromnewjersey
ID: 35207529
Thank you very much
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now