Thread: Convert Excel Flat File Into Relational Database

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.

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

Originally Posted by 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.

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.

Originally Posted by 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?
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.

Originally Posted by 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

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.

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

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

Originally Posted by 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

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

Originally Posted by 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.

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.