Breaking out large table

K

KCFanatic

I have a large table in Access (900K rows) that I would like to seperate into
several tables or queries. Is there a way to write a query that selects rows
1-100,000?
 
J

Jeff Boyce

Why? As in "why do you care how many rows the table has?" With proper
indexing and data/table design, there should be few performance issues.

If you open a new query, select the table, add the fields you are interested
in, and add the field you are using to determine row number (NOTE: Access
doesn't "number" rows ... you have to add a field that imposes some sort of
sort order).

Now change the properties of the query -- right click in the open gray space
and select the TOP 100000 rows. Again, note that this will be based on
whatever you've told Access to sort by. If you haven't, there's no
guarantee you'll get the same 100000 rows the second time you run the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

KCFanatic

This isnt an Access performance issue. I want to break this out because I
need to import this table into another application that seems to have trouble
with large uploads. I tried to add a auto number field but I get an access
error that tells me I need to edit the registry and this is not something I
want to do. Im just wondering if I could create a query that says take
records 1-100K and so on

Thanks
 
K

KCFanatic

I thought about that but their are just to many combinations. I was hoping
there was a way to select records 1-100K and so on to make sure I got all the
records.
 
J

Jeff Boyce

Please re-read my response. You can get the "TOP 100000" if you tell Access
how to sort/order the rows. If you don't tell Access, or there isn't a way
to sort the rows, you won't be able to use a query to do this.

If you want to use Autonumbers, you could:
1. create a new empty table with an autonumber primary key field and all
the other fields you need
2. create an append query from your current table to the new table - run
it.
3. use the new table and the Autonumber values to pick off the range of
records you wish to export.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting Multiple Tables 1
Hitting the limits with large table in Excel containing 1 millionrecords. What is the best option fo 2
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Dealing with a Pivot Table (when I don't want to) 0
Help creating a search form in Access XP 0
Creating a Search using a combo box and then creating a report. 4
Setting column width in a table with a macro is not working 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0

Top