Database as Spreadsheet

[pjm]

I have not designed a database before, I have done queries, forms, reports
etc from db's that have already been set so I am in new territory here.....

I have inherited a database that was set up as a spreadsheet. I need to
break out into tables and know I need to pretty much start over and do that
but does anyone have suggestions on how I can populate the new tables with
the old info without having to manually redo all the records/data? Is there a
better way of taking a table and splitting it up into several tables? Any
help would be welcomed!

 

[mscertified]

You can import a spreadsheet to a table, so if you have separate spreadsheets
you can import to separate tables. To split data in one spreadsheet into
multiple tables, create multiple spreadsheets first. To split data in a
single Access table, write queries or VBA code, the former is easier but less
powerful.

-Dorian

 

[John W. Vinson]

I have not designed a database before, I have done queries, forms, reports
etc from db's that have already been set so I am in new territory here.....

I have inherited a database that was set up as a spreadsheet. I need to
break out into tables and know I need to pretty much start over and do that
but does anyone have suggestions on how I can populate the new tables with
the old info without having to manually redo all the records/data? Is there a
better way of taking a table and splitting it up into several tables? Any
help would be welcomed!

I'd suggest the Access Table Analyzer wizard... and it may be worth trying...
but it's no substitute for a human brain doing a proper normalization.

You can certainly create normalized tables and then run (probably quite a few)
Append queries to migrate the data from the wide-flat table into your new
structure. The details of how to do this depend on the details of your
database of course - if you post some specific examples we may be able to
help.

John W. Vinson [MVP]

 

[Jerry Whittle]

Data migration from an old system to a new one is often painful especially if
the old system wasn't designed well. Some ways of doing it follow:

1. Create the database and try to import as much data as possible. Manually
re-enter data that can't be readily imported. This is known as a long
weekend. Test, test, and retest all your queries for importing data into the
new table structure and make sure the application works right on a
non-production database until you are sure things will work right. The last
time I was involved in such a project, we spent much time in cleaning up the
existing data as it was garbage in the old system. Things like dates 200
years in the past or text in number fields.

Note: Do not make compromises with the design of the new database to make
reusing old data easier except as the last option.

2. Clean break. On a certain day start using the new, empty database. Keep
the old one around only for research.

3. Duel systems. Keep both databases up and running until the old one is no
longer used. For example all the old orders are fulfilled.

 

[Pat Hartman]

Having done this dozens of times, I don't envy you. I generally start by
importing the spreadsheet as is. If it doesn't import cleanly, I create a
table with the problem columns defined as text. Excel is very flexible as
to what data it allows in each cell but relational databases, due to their
nature, expect all data in each column to be of the same type. So if the
first 30 odd rows are valid dates, the Access import wizard will expect all
rows to contain dates and define the column as datetime. If some of the
rows contain "TBA" or some other non-date data, the import will generate
errors. The two ways to solve this are to modify the table definition
yourself and append to an existing table rather than creating a new table or
go into the spreadsheet and add a new first row of data with values that
indicate the type of datatype you want Access to define.

Once you have the whole spreadsheet imported, the fun begins. You now have
to analyze the data to determine what you have for sets. Take a customer
table for example. I would run a series of group by queries. Group by
Company name. Group by Company name and street address. Group by State.
Group by Customer type. This will help you find duplicates that are
slightly off. One name might have a coma before the LLC designation and
another might not or might have periods separating the letters - L.L.C.
Depending on the size of your recordset, you may be able to catch these
visually after the grouping or if the set is large enough, you might need to
create some fuzzy matching programs or buy commercially available products.

Once you have cleaned up the spreadsheet data and have some idea of what
your entities are, you need to start the load process. I take my Group by
company and address query and make that an append query to append new rows
to the company table. I then add a CompanyID column to the spreadsheet. I
run a query that joins the company table to the spreadsheet on all the
columns of the company table and I update the CompanyID in the spreadsheet
with the autonumber that was generated as the new CompanyID. That will end
up being the foreign key for the next level of data. And so on until it is
done.