Converting Excel spreadsheets to an access database

[mikael.lindqvist]

Hi everyone,

Although I understand the basic principles on SQL database I'm
nevertheless stuck on how to make this one work in practice.

WHAT I'VE GOT
1. I've imported four tables (table1,,...,table4) from Excel to Access.
2. Each of the four tables share 3 common data (month, type1, type2).
3. Additionally there are some further "sharing" of three data "sets"
but it's not common for all 4 tables.

WHAT I'M TRYING TO DO
1. Build a complete database without any redundant data.

Since the spreadsheets are big (> 100,000 lines) I cannot make ONE
giant dataset and use Access' analyze tool to break out the redundant
fields.

SPECIFICALLY...
I have broken out "month" from each table - using the "analyze tool" -
(month1, month2, month3, month4), each is 1-N relationship with
table1,...,table4.

Now, I really need to "merge" this four tables (month1,...month4) into
one table that is 1-N relationship with table1,...,table4 ("month",
ie, jan-dec, is one of a few common data that binds together my
original four tables.

How do I do that? That is, re-arrange the month tables into one (I have
fiddled around a lot in the "relatins-window" but no luck).

If I was building the Access database from scratch (bottom-up) I'd
basically make 1 month table (jan, feb,...,dec) and then enter data.
Now I've got it the other way around; I have data and I need to break
it down to make it non-rendundant.

Any help as always appreciated (even more because none of my 2 books on
Access nor any online resource seem to cover this topic).

Cheers,
Mikael
Sweden

 

[Bill Mosca, MS Access MVP]

Mikael

By redundant data are you referring to duplicate rows? If so, creating a
unique index on an empty table and then appending all the rows into that
table should eliminate the dups.

 

[mikael.lindqvist]

Hi again,

I probably didn't do a very good job explaining what I'm attempting to
do :>

Anyhow, I put a small part of the excel file on a webpage (removed most
of the data, but kept the columns intact).

http://www.heleneholmstriteam.se/dev2/excel_to_access_data.xls

EXPLANATION
First page (original data) contains everything I want in the Access
relational database. It's a huge flat file. Problem is that it contains
too much information and when I tried to break out the data (after
importing it to Access) using "analyze tool" I get error telling me to
"increase the file lock index... something".

Anyhow, I then broke out the "original data" into 4 different pages.

* Orange header (mån, resultatenhet, typ) is common for all 4 pages /
tables.
* Green header needs to be normalized (it contains multiple data)
* Header without color is the "data".

Cheers,
Mikael

###############

Bill Mosca, MS Access MVP skrev:

 

[Guest]

I do not go to unknown websites.

You still did not say what your data was for.
Post some of the data as it is and tell us what you are trying to get out of
it ( an example of your desired results).

 

[mikael.lindqvist]

I'm building the Access database for data-analysis (pivot-tables,
reports etc).

The data is collected on 60 different units, measuring their
productivity (2 types) and resources (2 types) over time (monthly
periods).

Not sure if that helps you "helping me" but what the heck :>

Cheers,
Mikael

KARL DEWEY skrev: