You have a lot of duplicated information which can be eliminated.
Decide on how many tables you need and then import the data into the
separate tables elminating the duplicates.
Then you can write a small routine to link the records with the correct
primary keys and then set up your table relationships.
I'm assuming this is a one-off operation and you will maintain the data in
your db.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"ChicagoPete" wrote:

So my company just bought another company and they send me a flat text file
with 1.5 million records of customer name/orders/transactions etc - all in
one huge txt file. Just for fun... I imported this into Access 2007 and you
can view the single table, can't do anything with it, a simple parameter
search on one customer name locks up the server, Access not responding, white
screen blah, blah blah...

I tired to split the table during import with the wizard to create the 6 or
so tables I would like to have - but it kept bombing out. When I tried a
small sample I see I get prompts for nulls fields and am asked to "leave as
is?" a lot, so I guess when I tried to import the complete file that is where
it is hanging up. Don't feel like cleaning 1.5M records today.

Do I set this up on a SQL backend? If so, do I create the SQL db first then
import the text file into SQL OR do I import the text file into Access and
use the db splitter to create the SQL side? Is there a way in SQL to split
into seperate tables?

here are the fields - (tab delimited in file, i used comma here for ease):
Acct Num, Cust Name, Address1, City, State, Zip, Service Call Date, Item,
Qty, Amt, Pmt Date, Service Tech, Ins Name , Ins Paid, Balance

Basically I see about 20 rows per customer name, each with a different Pmt
Date (they pay on the accounts), looking for a ledger type report to run
based on Customer name that will show all payment dates and current balance
due.

example
48714 Smith, Mary 123 Main Boston MA 02134 02012010 937821a 1 2,500
02012010 Bob-11 2,000

48714 Smith, Mary 123 Main Boston MA 02134 02012010 937821a 1 500
03012010 Bob-11 1,500


The intent is to use this data as read only, no new transactions will be
posted, will just need to view previous balances for customers.

Open to any and all ideas on this one...