convert a flat file to a relaitionl data base

[Guest]

i have a flat file imported from excel i need help how to convert the flat
file to a relational database
there are 10000 sku numbers with only 50 departments (each sku have a
department number)

 

[Joseph Meehan]

i have a flat file imported from excel i need help how to convert the
flat file to a relational database
there are 10000 sku numbers with only 50 departments (each sku have a
department number)

That's a question that sounds a lot easier than it is. In fact it may
be two questions.

Do you need to know the mechanics of how to do it or do you need to know
what fields go where?

The second one is the tough one, it is as much an art as a science.

However MS was nice enough to build in a wizard to help you get started.

The process is called normalizing and the XP versions has the following
in its help file.

Split a table into related tables

On the Tools menu, point to Analyze, and then click Table.

Follow the instructions in the steps of the Table Analyzer Wizard.

Note In a Microsoft Access project, the tables reside in a Microsoft SQL
Server database; therefore, the Table Analyzer is not available.

 

[James Hahn]

Why convert? You can use that data as a 'flat' file within Access by simply
importing the whole file into a single table. If it was good enough for
Excel it's good enough for Access.

Of course, if you are importing it into Access in order to expand on the
functionality that you had when it was an Excel file, then the process you
will use and the design changes you will make depend on what extra
functionality you are trying to achieve. Without knowing that, your
question is unanswerable.

 

[Guest]

i need the skus grouped by deparments

Why convert? You can use that data as a 'flat' file within Access by simply
importing the whole file into a single table. If it was good enough for
Excel it's good enough for Access.

Of course, if you are importing it into Access in order to expand on the
functionality that you had when it was an Excel file, then the process you
will use and the design changes you will make depend on what extra
functionality you are trying to achieve. Without knowing that, your
question is unanswerable.

 

[John Vinson]

i need the skus grouped by deparments

I'd normalize the data, but if that is ALL that you need, a single
table would work fine. You can create a Query based on the table,
sorted by department; or, you can create a Report, and use its Sorting
and Grouping property to group by department, displaying all the SKU's
on each department's detail section.


John W. Vinson[MVP]

 

[James Hahn]

Then sort it into department order, just like you used to do in Excel.

You can also use the reporting facilities in Access to create a report that
prints SKUs by department, for one department or for all. None of this uses
the relational features of Access.

 

[Joseph Meehan]

i need the skus grouped by deparments

:

If that is it, Excel can do that.

 

[Mike Painter]

i have a flat file imported from excel i need help how to convert the
flat file to a relational database
there are 10000 sku numbers with only 50 departments (each sku have a
department number)

To answer your question, yes there is a tool that will examine your table
and split it. Tools/Analysis/table

 

[Guest]

Thank you
The analyze tool did a wonderful job (I used No, I want to decide) but I
would like to know how to do it myself
"what's the query for that?"

 

[Mike Painter]

Thank you
The analyze tool did a wonderful job (I used No, I want to decide)
but I would like to know how to do it myself
"what's the query for that?"

It's easier to do than explain.
Work with a copy of your data base. (Ctrl-C, Ctrl V) and keep the data,
might as well do it twice.

Lets say you have name, address, company, company address, company phone,
etc.
Work with a copy.
Open in design view and remove all the fields that don't deal with the
company.
Create a copy of this with no data. Name it Companies. Add a company ID
field make it an auto number.
Make the company field indexed, no duplicates in companies.
Append the copy to Companies.
Now you have a Companies table and will need to review it to make sure that
CompanyA is always spelled the same. Delete duplicates.
Now go to another original copy and delete all the company fields except the
single company field. Add a company ID field.
Join the two on the *company* field.
Use that query to update the company ID in the first copy with
companies!companyID.
Delete the company field from the copy.

Do this for all other tables you want to split off.

You'll end up with two tables.
One will have name, address, companyID
the other CompanyID, company, company address, company phone