Good references for Flat-Database conversion?

[Brian]

My Access books do a pretty good job of telling you how to design and create
a database from scratch, using lots of tables, primary keys, etc.

I've found that attempting to convert a flat database (i.e. Excel file) into
an Access database is much harder to do. Often times I've found the books are
of no help, because no Access book is telling you how to create a flat
database. Thus, I cannot just reverse the process and have the database I
want. Depending on how poorly assembled the flat database is, I envision
hours of work involved.

I am looking for some good website resources and/or books to help me
accomplish this task. Before you suggest I do so, I have already tried using
the Access Table Analyzer Wizard. I've found the wizard is too dumb to
understand what is going on for my purposes. Therefore, I have no choice but
to do everything manually and this is where I could use some generic help
with decent illustrations and descriptions.

I appreciate your help.

 

[gls858]

My Access books do a pretty good job of telling you how to design and create
a database from scratch, using lots of tables, primary keys, etc.

I've found that attempting to convert a flat database (i.e. Excel file) into
an Access database is much harder to do. Often times I've found the books are
of no help, because no Access book is telling you how to create a flat
database. Thus, I cannot just reverse the process and have the database I
want. Depending on how poorly assembled the flat database is, I envision
hours of work involved.

I am looking for some good website resources and/or books to help me
accomplish this task. Before you suggest I do so, I have already tried using
the Access Table Analyzer Wizard. I've found the wizard is too dumb to
understand what is going on for my purposes. Therefore, I have no choice but
to do everything manually and this is where I could use some generic help
with decent illustrations and descriptions.

I appreciate your help.

I think you 've already answered your own question. While it's relativly
easy to import a simple spreadsheet into Access from Excel,
say a list of names and addresses, a complicated spread with many
calulated fields and forumlas just doesn't work. Excel and Access
handle data in afundementally different manner.

If you could give a little more info on the type of data you want to
import and how it's laid out in the spreadsheet maybe someone here
could help.

gls858

 

[Brian]

I know that ideally you shouldn't "plan" for such a conversion as it becomes
too messy. Sometimes though, I am simply screwed over and have no other
choice but to make a conversion. For instance, most businesspeople today are
pretty familiar with using Excel. Unfortunately, the majority of these people
have never used Access and are intimidated by it.

For the time being, let's just assume that the calculations are extremely
simple (addition, subtraction, division, multiplication, etc.), that is, if
any calculations are used at all. I'm more interested in establishing
relationships and eliminating the flat database characteristics of Excel as
much as possible.

 

[John Vinson]

I know that ideally you shouldn't "plan" for such a conversion as it becomes
too messy. Sometimes though, I am simply screwed over and have no other
choice but to make a conversion. For instance, most businesspeople today are
pretty familiar with using Excel. Unfortunately, the majority of these people
have never used Access and are intimidated by it.

For the time being, let's just assume that the calculations are extremely
simple (addition, subtraction, division, multiplication, etc.), that is, if
any calculations are used at all. I'm more interested in establishing
relationships and eliminating the flat database characteristics of Excel as
much as possible.

You must still go through the (yes, imtimidating if you've never done
it) exercise of logically analyzing your information; identifying the
Entities, their Attributes, and their Relationships; and creating a
table for each entity, with a field for each atomic non-repeating
non-derived attribute. Excel experience won't help much here, simply
because the concepts of normalization aren't particularly relevant in
that programming environment!

The task of *migrating* the data from a wide-flat spreadsheet to the
(multiple) normalized tables is (if you're lucky) rather pedestrian;
just a series of Append queries, some of them maybe with a join to
pick up a newly generated primary key value. Sometimes a hassle but
rarely does this job pose the mental challenge of actually thinking
about the nature of the information and constructing a data model!

John W. Vinson[MVP]

 

[Allan L]

I've been working on a similar challenge as Brian describes. I have the
design mapped out for where I want to break up the flat-file for
normalization, and I'm fine with using Append queries with joins to make the
links, but I'm stuck on how to populate the initial table that
concatenates/collapses duplicated original field values into one record per
unique value... so that I can generate a primary key to link the related
tables to.

I know this can be accomplished quite simply by choosing my own breaks via
Table Analyzer. But I am trying to avoid using the Table Analyzer or Lookup
Wizard due to the recommendations throughout this bulletin, and additionally,
I would like to eventually automate the import process.

Given that the fields to be split (migrated) from original table are known,
does anyone have any suggestions on how to accomplish the same task as the
Table Analyzer in a more elegant fashion? I'm sure this must be a very simple
step that I am just overlooking, but if someone could lead me through my
humility, I'd greatly appreciate it!

Also, as per Brian's initial request, if anyone knows of any web references
that further describe these sorts of tasks, that be great as well!

Thanks for your advice! - A

 

[John Vinson]

I'm stuck on how to populate the initial table that
concatenates/collapses duplicated original field values into one record per
unique value... so that I can generate a primary key to link the related
tables to.

My suggestion would be to create your normalized tables, WITHOUT the
relationships (for now, you'll add them later).

Use a SELECT DISTINCT Append query to collapse the repeating data in
your flatfile into just unique records for your "one" side table (or
tables, you'll probably want to create a variety of 'lookup' tables in
this way). If you have a "natural key" included in your data (some
field that is unique, stable, and reasonably small) make it the
primary key; if not you can include an Autonumber primary key in your
target table, and then use update queries linking your master table to
the child tables by whatever combination of fields make the linkage to
populate the foreign key field. It can be tricky and involve a fair
bit of manual work!

Once you have the lookup tables, master table, and child tables filled
in, you should be able to create the relationships and enforce them.

John W. Vinson[MVP]