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?