Limit number of records returned from query

[David G.]

Can I limit the number of records returned by a query to the 10 most
recent qualifying records? recordset is read only, the date spanned
would vary, so I can't use that field to limit record count.
THANKS!
David G.

 

[dweeber62]

I think I found the answer:
http://office.microsoft.com/en-us/access/HA100963091033.aspx?pid=CH100645771033
Hidi Ho,
The Dweeber

 

[Dirk Goldgar]

Can I limit the number of records returned by a query to the 10 most
recent qualifying records? recordset is read only, the date spanned would
vary, so I can't use that field to limit record count.

Have the query sorted into descending order on the date field, and use the
TOP 10 predicate. For example:

SELECT TOP 10 MyTable.* FROM MyTable
ORDER BY MyTable.DateField DESC

Note, though, that if there can be multiple records with the same date, you
could potentially get more than 10 records returned, because there would be
no reason to distinguish the 10th record from the 11th if they have the same
date.

 

[Ray]

I'm not sure what you mean by the date being "spanned". But assuming you
have a table with names and qualification dates, the following should work:

SELECT TOP 10 Name FROM QualificationTable
ORDER BY QualificationDate DESC

It would give you the 10 records with the largest (most recent)
qualification date.

Ray

 

[David G.]

Good point. In my case, if the record count goes to 14 or 15 because
of duplicate days, it won't matter. I just want to minimize network
traffic.

Thanks for your thoughts!

Have the query sorted into descending order on the date field, and use the
TOP 10 predicate. For example:

SELECT TOP 10 MyTable.* FROM MyTable
ORDER BY MyTable.DateField DESC

Note, though, that if there can be multiple records with the same date, you
could potentially get more than 10 records returned, because there wouldbe
no reason to distinguish the 10th record from the 11th if they have the same
date.

THANKS!
David G.

 

[dweeber62]

Ray:
spanned=the length of time between the 1st record and the 10th record
of the desired recordset.

i.e. If I knew that 1 record is created each day, I could limit the
record set using the date field. <=Today AND >=Today-10 days.

Your solution is similar to what I found, and Dirk's. I modified the
query to TOTALS query, changed the date column to "MAX", then viewed
the query in SQL so I could change the limit to top 10.

Since I also displayed the date in my form, I had to add the date
field column a second time, leaving the 2nd column to GroupBy.

Thanks for your comments.

I'm not sure what you mean by the date being "spanned". But assuming you
have a table with names and qualification dates, the following should work:

SELECT TOP 10 Name FROM QualificationTable
ORDER BY QualificationDate DESC

It would give you the 10 records with the largest (most recent)
qualification date.

Ray

Hidi Ho,
The Dweeber