Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Breaking out large table

Discussion in 'Information Technology' started by KCFanatic, Jul 28, 2006.

  1. KCFanatic

    KCFanatic
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    USA AP Breaking News: Sikhs Added to Hate Crime Stats Breaking News Aug 3, 2013
    Pacific Scandals breaking like China dolls Breaking News Jul 10, 2013
    1984 Bluestar: Breaking the Wheel of Dharma History of Sikhism Jun 4, 2013
    India BREAKING NEWS: Growth needs hard decisions: PM Breaking News Sep 22, 2012

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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" <KCFanatic@discussions.microsoft.com> 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?
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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
    is just because you think it is too large, you may want to re-think. Are
    you having problmes?


    --
    Joseph Meehan

    Dia duit
     
  5. KCFanatic

    KCFanatic
    Expand Collapse
    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

    "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" <KCFanatic@discussions.microsoft.com> 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?

    >
    >
    >
     
  6. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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
     
  7. KCFanatic

    KCFanatic
    Expand Collapse
    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" 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
    >
    >
    >
     
  8. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    KCFanatic wrote:
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  9. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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

    "KCFanatic" <KCFanatic@discussions.microsoft.com> 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
    >> query.
    >>
    >> Regards
    >>
    >> Jeff Boyce
    >> Microsoft Office/Access MVP
    >>
    >> "KCFanatic" <KCFanatic@discussions.microsoft.com> 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?

    >>
    >>
    >>
     

Share This Page