Limit retrieved records based on "record count"

[KenG]

I would like to limit the number of records I retrieve in my SQL query to, for example, the first 1000 records in the results set. I could do a make table query and then manually select and delete everything after the 1000th record, but I would rather do it programatically. An alternative would be to be able to create a calculated field which would increment by 1 on each row which is returned and then using the where clause to get rid of the records over my limit. Any ideas would be appreciated.

 

[Andrew Smith]

SELECT Top 1000 ...

sounds too simple - I must have missed something!

I would like to limit the number of records I retrieve in my SQL query to,

for example, the first 1000 records in the results set. I could do a make
table query and then manually select and delete everything after the 1000th
record, but I would rather do it programatically. An alternative would be
to be able to create a calculated field which would increment by 1 on each
row which is returned and then using the where clause to get rid of the
records over my limit. Any ideas would be appreciated.

 

[Brian Camire]

In SQL, you might use the TOP predicate, as in something like:

SELECT TOP 1000
[Your Table].*
FROM
[Your Table
ORDER BY
[Your Table].[Some Field]

In DAO or ADO, you might instead use set MaxRecords property (either of a
DAO QueryDef or an ADO Recordset) to 1000 before opening a Recordset.

Either way, without an ORDER BY clause, the records returned with be
arbitrarily selected.

I would like to limit the number of records I retrieve in my SQL query to,

for example, the first 1000 records in the results set. I could do a make
table query and then manually select and delete everything after the 1000th
record, but I would rather do it programatically. An alternative would be
to be able to create a calculated field which would increment by 1 on each
row which is returned and then using the where clause to get rid of the
records over my limit. Any ideas would be appreciated.