View first 1000 records in msaccess

[Guest]

I am trying to select first 1000 records in msaccess and make another table.
Trying to find access equivalent of

Select * from table for recno()<1000 (VFP)

 

[Guest]

Try this
select Top 1000 * from Table

 

[Rick B]

What do you mean "first"?

Access does not store records in any particular order. You would have to
have some field that you would want to use to determine which records to
pull.

Second question - why do you want them in a new table? If you want to use
them in a report, query, form, etc. just use the query as your record
source. Genrally, you should not need to take records out of a table and
create a new table with them.

 

[Michael J. Strickland]

1. Copy the table (structure only) to a new table (Select table, Ctl-C,
Ctl-V).
2. Make a query on the original table, selecting the 1st 1000 records ([ID]
<= 1000).
3. Open the query and copy all records to the clipboard (click top left
corner, Ctl-C).
4. Open the new (empty) table.
5. Paste clipboard contents into the new table (Click the top left corner,
Ctl-V).

 

[aaron.kempf]

Access _DOES_ store records in a particular order.. right?

 

[Rick B]

nope.

Tables are simple heaps of records. You cannot rely on the records to be in
any particular order.

You can move records around in the table.

You can sort the data, but it is not stored in any particular way.

 

[Douglas J Steele]

The order isn't predictable, and should never be relied upon, as it can
change when you compact the database.

 

[Guest]

By default, records are saved in sequence, then sorted to primary
key order when you compact the database. Simple dynasets
('select * from table') and table-type recordset normally retain
this order - which if you normally insert records in primary key
order is just consistent enough to be really dangerous.

And if you don't normally insert in primary key order, the table
will be a mixture of primary key order (from the last compact)
and sequence order (from all later data entry).

With more general queries, Access may sort the records so that
it can aggregate or do joins. And sub-queries do not always retain
query order. In those queries, the recordset order is unrelated to
the table order.

(david)

 

[aaron.kempf]

yeah thanks i thought that there was some default sorting/storage in
Access