#  Other Applications & Softwares  > Access Tables & Databases >  >  Convert Excel Flat File Into Relational Database

## crazysniper

Hi Everyone, Hope you all had a great New Year. 

I have been tasked to convert an Excel Flat file into a Relational Database (sort off) to keep track of Shipments. The headings are already provided (Please see the attached excel file for more info) 

I'm a beginner on this subject. So I began grouping the headings first into sort of a related fields and the idea is to create a table base on the groupings and linked them together in Access (Relationship) 

This is what I have come up so far Normalizing the headings.


EXPORTER
-----------------------------------
-ExporterID
-Name


CUSTOMER
-----------------------------------
-CustomerID
-Region
-Country
-PO Number
-Bill of Lading


SHIPMENT
------------------------------------
-ShipmentID
-CustomerID
-ExporterID
-ContainerID
-Destination
-ETD
-Term
-ETA
-Period

CONTAINER
------------------------------------
-CointainerID
-Batch No
-Number of Palletes
-Net Weight
-Gross Weight
-Volume (Cubic Meter)
-Container Type
-Pieces

My question is did i normalize it right? If not i would really appreciate your input on these.

By the way it might be worth mentioning this. When i was looking at the Data on the Excel file. I have noticed that the Batch Number and Container columns have more Data on it. Meaning the Shipment on that day did not only contains more Batches of Item, but also has more Containers.

I am really hoping someone here can kindly help me putting this together. Thank you so much in Advance.

----------


## crazysniper

No one :-( Come on guys.. I would really appreciate your input on this.

----------


## alansidman

Crazy;
Your layout looks good initially.  In your flat file, it appears that there are multiple entries in some of the cells.  In Access this is not acceptable, you will need to create multiple records as for the associated fields.  Since you are a beginner, I urge you to look at a couple of websites that I found very helpful when I started doing databases.

http://www.bluemoosetech.com/index.php
http://www.datapigtechnologies.com/AccessMain.htm
http://www.datawright.com.au/
http://www.techonthenet.com/index.php
http://www.paragoncorporation.com/Ar...x?ArticleID=27

Good luck with your project and post back as you need help.
BTW, If most of your data is currently in a flat file, you can separate the file into files that mirror your tables and import the data into Access.  This will save a lot of data input time when you are ready to populate your tables.

Here is my favorite write up on normalization.
http://forums.aspfree.com/microsoft-...es-208217.html

Alan

----------


## crazysniper

> Crazy;
> Your layout looks good initially.  In your flat file, it appears that there are multiple entries in some of the cells.  In Access this is not acceptable, you will need to create multiple records as for the associated fields.  Since you are a beginner, I urge you to look at a couple of websites that I found very helpful when I started doing databases.



Yup, i have identified this issue and this is why I am determine to covert this flat file into a relational one. My goal is to be able to identify how many container was used in a particular shipment and in those container what are the batches that went inside. 

If you can help me a little bit on how do to create multiple records for the associated field i would really appreciate. 

I have been spending time with my normalization and I have now come up with the following entities. 


EXPORTER
------------------------------
Exporter ID *PK
Name


CUSTOMER
------------------------------
Customer ID *PK
Region
Country
PO Number
Bill of Lading


SHIPMENT ID
------------------------------
Shipment ID *PK
Customer *FK
Exporter *FK
Container *FK
Period
Term
Destination
ETD
ETA

CONTAINER
------------------------------
Container ID *PK
Shipping Line
Batch *FK
Container Size
Palletes
Net Weight
Gross Weight
Volume (Cubic Meter)


ITEM
-----------------------------
Item ID *PK
Batch Number
Type
Pieces

Mr alansidman is there improvement on this design? I really appreciate your input on this.

Thanks and also for the Links.

----------


## alansidman

I see that you have a foreign key for Batch in the container table.  What will that look to join to?  In what table will that link to and what field?

It is a good rule in databases to not have spaces between words that identify a field.  ie. Gross Weight should read either Gross_Weight or GrossWeight.  I prefer without the underscores.  You should not use any characters such as the parens.  Access will not like this.  Also you not use any reserved words such as Name, Date as this will cause problems in the future.  Look at this link for a listing of reserved (no-no words)

http://support.microsoft.com/kb/209187

Alan

----------


## crazysniper

> I see that you have a foreign key for Batch in the container table.  What will that look to join to?  In what table will that link to and what field?
> Alan



Thanks again for replying Mr alansidman i really appreciate your effort to helping me. 

The FK on the Container table will be linked to the PK on the Item table. I am not sure yet if that is the right thing to do. I figured a shipment can have number of Container(s) and Container(s) can have number of Batches inside.

My idea is, later when all the tables are linked. I want to be able to query my database and ask the following questions.

How many container was in that particular shipment
What Batches are included in the container(s)

With your expertise will i be able to achieve this with my current plan? If not i would appreciate your input on this.

Thanks once again.

----------


## alansidman

Seems reasonable.  I suggest you build your tables, then your input forms, populate your tables with sample data and then begin to create your queries.

Alan

----------


## crazysniper

> Seems reasonable.  I suggest you build your tables, then your input forms, populate your tables with sample data and then begin to create your queries.
> Alan



The tables are done including the relationships etc. But i am unsure about the relationship I have between the Containers and Items??

Would you mind Testing my DB to see i am able to achieve my goals please. I need another persons opinion.

Thanks Mr Alansidman.. 

The DB i have so far is attached.

----------


## crazysniper

Opps. Take two.. why is the file not being attached.

----------


## crazysniper

Another try. Still system does not allow my attachment. sigh!

----------


## crazysniper

Mr Alansidman,

Would you mind checking Please if my Table Relationships are correct? I would love for you to test my DB but the upload doesnt seem to work. 

Thanks

----------


## alansidman

Appears to be ok. Personally, I don't usually link the tables until I do my queries, but that is just a personal style.  Looks like you are on the right track.
In trying to upload your db, did you zip it first.  The system here will not accept unzipped access data bases.
Alan

----------


## crazysniper

> Appears to be ok. Personally, I don't usually link the tables until I do my queries, but that is just a personal style.  Looks like you are on the right track.
> In trying to upload your db, did you zip it first.  The system here will not accept unzipped access data bases.
> Alan



I see.. Thanks for the tip Mr alansidman. I did zipped the file prior to uploading but it did not work for me last time i tried.

Anyhow, the uploading process is working now and i would really appreciate if you could test my DB for me to see if I am able to accomplish my main objectives before i start inputting the historical data.

The test DB is attached with this post.

Thanks

----------


## alansidman

Add a primary key to your EuroCountry table.  Make sure that all your links are the same type.  Some are Long integers for the PK and Integers for the FK. Otherwise you will end up with mismatches when you attempt to run your queries.  I suspect that this will be a lookup on your input form and you will want to do it using the ID.  Otherwise, it appears ok.

----------


## crazysniper

> Add a primary key to your EuroCountry table.  Make sure that all your links are the same type.  Some are Long integers for the PK and Integers for the FK. Otherwise you will end up with mismatches when you attempt to run your queries.  I suspect that this will be a lookup on your input form and you will want to do it using the ID.  Otherwise, it appears ok.



Thanks again Mr AlanSidman. The EU country are just for reference so i wouldnt worry about adding PK on that. 

I am struggling to create an input forms. I wanted to create one form where you can input all the details required for each table but when you go form wizard it only accepts one table at a time. Can you give me assistant as how can i achieve this please?

Also I would gladly appreciate if you can try to use my DB and input some data and give me feedback as a user of the DB.

Thanks again.

----------


## alansidman

If at all possible, avoid using the wizard to create forms.  Open a blank form in design.  Bind the form to a table.  Then add each of the fields you wish to have using the field listing.  You can only bind a form to a single table or query.  If you have more than one table you wish to input to contemporaneously, then you will need to create a separate form for each table. Make these separate forms sub forms and place them in the main form.  If you have created relationships between the tables, then you should be able to link the Parent form to the child forms.  Here is a good resource on all these topics.

http://www.databasedev.co.uk/general.html

I would urge you to pick up a book on Access as this will help you to picture these concepts and expand your understanding.

As to testing your database, I personally do not have time or inclination to do that.  I am willing to guide you in your endeavor and answer questions as issues arise, but I will not test your work for you.

----------

