Select top X records in a query

[KMdouglas]

I have a table from which I want to select the X most recent entries. Usually this is not a problem, I can set the query to select the top X records. My problem is that I want to select more entries based on the number of records in the table. Specifically here is what I want.

1 to 6 records in original table select top 1 record
7 or 8 records = top 2
9 or 10 records = top 3
11 or 12 records = top 4
etc.

Does anyone know of any way to do this.

Thanks in advance



Kevin M. Douglas
kevin@easternresearch.com

http://www.easternresearch.com

 

[KyleS]

You could use DAO and get the QueryDef and rewrite the SQL before you run the query.

Dim qdf As DAO.QueryDef, intX As Integer
Set qdf = CurrentDb.QueryDefs("query5&quot

intX = DCount("FIELDNAME", "TABLENAME&quot
Select Case intX
Case Is < 6
intX = 1
Case 7 Or 8
intX = 2
Case Is > 10
intX = 5
'et cetera
End Select

qdf.Sql = &quot;SELECT TOP &quot; & intX & &quot; FROM .....&quot;

DoCmd.OpenQuery qdf.Name
qdf.Close
Set qdf = Nothing Kyle