Relational database design for flat text file

[Rich]

I'm not the most experienced at using Access but I do know a fair amount. Can
I design myself a relational database for an extremely large set of data,
currently "flat" in a text file, then using append queries populate the
appropriate fields and tables as necessary?

The Table Analyzer Wizard gives me problems when I try to convert the flat
file, so I'd rather use some other way. Other than making sure to handle the
primary keys properly, what other precautions or advice is recommended for
this process?

 

[Graham Mandeno]

Hi Rich

I would recommend that you import your flat text file into a flat table
first, then change the data types of fields as appropriate and check the
consistency of the data.

Then you can populate your "real" tables using queries based on this flat
table.

Start by populating the tables on the "one" side of the one-to-many
relationships. This can be done using an append query of the form:
Insert into <table> (<field list) select DISTINCT <field list> from
<flat table>

The "distinct" here is the important bit - it ensures that duplicates are
eliminated from the query.

Then work backwards, populating the related "many" tables.

You might wish to add surrogate primary keys (eg AutoNumbers) to your
reference tables. If so, then add the PK field to the table before you
populate it and it will generate the key values when you append the records.
Then you can add a temporary foreign key field to your flat table and
populate it using an update query where your flat table and the reference
table are joined by the unique fields you used in the DISTINCT query above.
Then that FK can easily be used to populate the FK field in any related
tables.