Breaking out large table

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?

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14004-breaking-out-large-table.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004

"KCFanatic" wrote in message
news:F966A926-DB47-461E-B9A6-02C654F066BD@microsoft.com...
>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?

KCFanatic wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
is just because you think it is too large, you may want to re-think. Are
you having problmes?


--
Joseph Meehan

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
Dia duit

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
records 1-100K and so on

Thanks

"Jeff Boyce" wrote:

> 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
>
> "KCFanatic" wrote in message
> news:F966A926-DB47-461E-B9A6-02C654F066BD@microsoft.com...
> >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?
>
>
>

KCFanatic wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
> wondering if I could create a query that says take records 1-100K and
> so on
>
> Thanks
>
> "Jeff Boyce" wrote:
>

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.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004


--
Joseph Meehan

Dia duit

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
records.

"Joseph Meehan" wrote:

> KCFanatic wrote:
> > 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
> >
> > "Jeff Boyce" wrote:
> >
>
> 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.
>
>
>
> --
> Joseph Meehan
>
> Dia duit
>
>
>

KCFanatic wrote:
> I thought about that but their are just to many combinations. I was
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
by sorting all the records and looking for any records coming before any
alpha-numerics.

--
Joseph Meehan

Dia duit

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
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

"KCFanatic" wrote in message
news:6155BA3D-13D8-4AB2-B8AC-F26037F35EB9@microsoft.com...
> 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
>
> "Jeff Boyce" wrote:
>
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14004
>> query.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "KCFanatic" wrote in message
>> news:F966A926-DB47-461E-B9A6-02C654F066BD@microsoft.com...
>> >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?
>>
>>
>>