Dividing a large file

[Guest]

I have a massive file that is over 114,000 records. I need to export it to
excel, but the file is so large that only half will export. Is there a query
where I can divide the file in half? Example on table will be Customers A-L
and the second table will be M-Z?
I tried to just copy and paste, but my computer was freezing up and wouldn't
allow me to cut and paste such large amounts of data. I would prefer not to
cut and paste multiple times and risk altering the data.

Thanks in advance!

 

[Guest]

Hi

Create a new query like this

SELECT TableName.Surname INTO NewTableName
FROM TableName
WHERE (((TableName.Surname) Between [Enter start letter] And [Enter close
letters]));

Change TableName to the name of the table that contains your data. Of ourse
you need to add other fields into the query as well ?

You could use this as the base for the export if you wish then it would be
something like this

SELECT TableName.Surname INTO NewTableName IN 'OtherDatabaseName.mdb'
FROM tblClients
WHERE (((TableName.Surname) Between [Enter start letter] And [Enter close
letters]));


Hope this helps

 

[George Nicholson]

What version of Excel? Before Excel 2007 there is a limit of 65,536 rows per
worksheet.
If that's the case you will *never* get the entire table contents onto a
single sheet. "..only half will export.." sounds about right.

Do you still want to export it?

If so, create and save 2 select queries.
Q1: Under CustomerName, Criteria: Like "[A-L]*"
Q2: Under CustomerName, Criteria: Like "[M-Z]*"

(run the 2 queries, make note of the # of records and make sure the sum
matches the # of records in your table. If they don't then you have
customers whose names falls outside A-Z: possibly a number, punctuation
character or a space. Try substituing "[ -L]*" for "[A-L]*" (replace A with
a space).)

Export those 2 queries to Excel. Probably easiest to export them to separate
workbooks and then copy/move one of the sheets to the other. For quick &
dirty exports, I find it easiest to simply open the query in Print preview
and use the "Analyze with Excel" option from the "Office Links" on the
toolbar.

HTH,