Breaking out large table

[Guest]

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?

 

[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

 

[Joseph Meehan]

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?

Why. It would be difficult to suggest how without knowing why. If it
is just because you think it is too large, you may want to re-think. Are
you having problmes?

 

[Guest]

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

 

[Joseph Meehan]

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

:

That's different. Do you have an existing filed that you could use?
Even an alpha filed could by used by selecting the first letter in a query.
eg. Like A* will return all those starting with A You could use several
letters as well eg Like X* or Like Y* or Like z* Don't forget to consider
nulls and blanks.

 

[Guest]

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.

 

[Joseph Meehan]

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.

I guess I am out of ideas. Would think that you could be nearly all the
records with 36 queries or 12 using two "or" statements for each one, then
you would only have the odd ball remaining which you should be able to find
by sorting all the records and looking for any records coming before any
alpha-numerics.

 

[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