save ~50,000 record table sorted from one million record table

[Pete317]

I am trying to create a smaller database table extracted from a rather large database table of slightly over 1,000,000 records (1MR), supplied by another person. Access 2000 will slowly open, sort, and/or filter the original 1MR table, but will NOT permit saving a smaller portion of the table under a different table name, for faster access later. I believe I've tried every method, including copy & paste - Access always saves either the original 1MR database table, or a query to resort the original table's 1 million records.

What can I do to partition this big database into smaller tables?

 

[Jeff Boyce]

Pete

In what form does the large DB exist? ?Access/Jet, SQL Server, Oracle,
text-file, ...?

Is that large DB indexed properly? Are you linking to the table from within
Access? Have you imported the table into Access? If imported, have you
indexed it on the fields you wish to use for sorting and selection criteria?

Aside from the speed issue, is there also a normalization/relational reason
why you would split apart the data? Won't that mean you'll have to look in
multiple places to answer a question (i.e., to query)?

 

[Pete317]

Jeff:

Thank you for your reply to my Access question.

I have been using Excel for almost two years. For the most part I have been using Excel to manage tables with about 40,000 record lines. I now have a database with 1,033,407 records in it that I have saved as an .mdb file in Access. I have been able to sort this file and then filter it, but have not been able to save the resultant table either by a Save AS or copy and paste or export. The result of most of these is 22 records pasted or the first 56k plus records of the original unsorted file in Excel as an export. I need to create some much smaller files of the resultant sort and filter to make this information readily usable as a sub set.

Thanks,

Pete

 

[John Vinson]

I have been using Excel for almost two years. For the most part I have been using Excel to manage tables with about 40,000 record lines. I now have a database with 1,033,407 records in it that I have saved as an .mdb file in Access. I have been able to sort this file and then filter it, but have not been able to save the resultant table either by a Save AS or copy and paste or export. The result of most of these is 22 records pasted or the first 56k plus records of the original unsorted file in Excel as an export. I need to create some much smaller files of the resultant sort and filter to make this information readily usable as a sub set.

Excel is a spreadsheet, an excellent one.

Access is a relational database.

THEY ARE DIFFERENT. Access is not "a big flawed version of Excel"; it
requires a different mindset and a different approach!

Access Tables are repositories for data; Queries take that data,
select particular fields ("columns") and/or records ("rows") into
dynamic datasets. It's very rare that you would do a copy/paste, or a
MakeTable query (the analog of creating a new worksheet in Excel) -
because it isn't necessary to do so.

You can create a Query on your 1M+ table; apply whatever criteria you
need to select the records you want to see; apply whatever sort
clauses you want to sort the (dynamic, not stored) records into order.
A Table *has no order*; it's an unsorted bucket of data, so you'll
need a Query to sort it in any case.

This Query can be exported (using File... Export) into a Text file, an
Excel spreadsheet (if it's under the 65K row limitation imposed by
Excel), used for reporting or even for another query.

In short - the Query is the tool of choice here. If you can describe
the data and how you want it subsetted, we may be able to help with
the proper syntax for doing so!