Table Normalization Help

M

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
I have a Freight table which I would like to find the most proficient approach in getting into this normalize state....Below is the sample Freight_Tbl and the second table is my goal for normalization. How can this be accomplish is my question....Thanks in Advance as always...

Freight_Tbl

[TABLE="width: 864"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]City_Dest[/TD]
[TD]State_Dest[/TD]
[TD]Plant[/TD]
[TD]City_Origin[/TD]
[TD]State_Origin[/TD]
[TD]Efffect_Date[/TD]
[TD]Currency[/TD]
[TD]Rate_4[/TD]
[TD]Rate_6[/TD]
[TD]Rate_BB[/TD]
[TD]Rate_BG[/TD]
[TD]Rate_AA[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Richmond[/TD]
[TD]Kaleen [/TD]
[TD]Texas,US[/TD]
[TD="align: right"]5/1/2015[/TD]
[TD]USD[/TD]
[TD="align: right"]55.12[/TD]
[TD="align: right"]23.21[/TD]
[TD="align: right"]45.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Seattle[/TD]
[TD]Washington[/TD]
[TD]Seattle,US[/TD]
[TD="align: right"]5/2/2015[/TD]
[TD]USD[/TD]
[TD="align: right"]48.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]13.55[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Petersburg[/TD]
[TD]Petersburg[/TD]
[TD]Virginia,US[/TD]
[TD="align: right"]5/3/2015[/TD]
[TD]USD[/TD]
[TD="align: right"]35.15[/TD]
[TD="align: right"]32.58[/TD]
[TD="align: right"]14.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Ft. Dix[/TD]
[TD]Ft. Dix[/TD]
[TD]New Jersey,US[/TD]
[TD="align: right"]5/4/2015[/TD]
[TD]USD[/TD]
[TD="align: right"]45.68[/TD]
[TD="align: right"]21.5[/TD]
[TD="align: right"]32.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Result_Tbl needed....

[TABLE="width: 690"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]City_Dest[/TD]
[TD]State_Dest[/TD]
[TD]Plant[/TD]
[TD]City_Origin[/TD]
[TD]State_Origin[/TD]
[TD]Efffect_Date[/TD]
[TD]Currency[/TD]
[TD]Rate_Type[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Richmond[/TD]
[TD]Kaleen [/TD]
[TD]Texas,US[/TD]
[TD="align: right"]5/1/2015[/TD]
[TD]USD[/TD]
[TD]Rate_4[/TD]
[TD="align: right"]55.12[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Seattle[/TD]
[TD]Washington[/TD]
[TD]Seattle,US[/TD]
[TD="align: right"]5/2/2015[/TD]
[TD]USD[/TD]
[TD]Rate_BB[/TD]
[TD="align: right"]13.55[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Petersburg[/TD]
[TD]Petersburg[/TD]
[TD]Virginia,US[/TD]
[TD="align: right"]5/3/2015[/TD]
[TD]USD[/TD]
[TD]Rate_6[/TD]
[TD="align: right"]32.58[/TD]
[/TR]
[TR]
[TD]Oxon Hill[/TD]
[TD]Maryland,US[/TD]
[TD]US-MA-Ft. Dix[/TD]
[TD]Ft. Dix[/TD]
[TD]New Jersey,US[/TD]
[TD="align: right"]5/4/2015[/TD]
[TD]USD[/TD]
[TD]Rate_BG[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Hi, you will need to identify the column(s) for the primary key of the proposed table first.
 
Last edited:
Upvote 0
xenou said:
Hi, you will need to identify the column(s) for the primary key of the proposed table first.
Click to expand...
My attempt is to just normalize the table so instead of having Rate's in each column, just have it in one column as Rate_Type and Rate...Do I need a primary key to accomplish this in Access?
 
Upvote 0
I think i was using the wrong terminology, instead of normalizing it, I just want to achieve the new design which is the second tbl instead of the first one....Sorry, learning Access on the fly with great people like the MrExcel Team...
 
Upvote 0
If you don't have a primary key in your table you can expect only pain and suffering from your database.
 
Upvote 0
If you don't have a primary key in your table you can expect only pain and suffering from your database.
Click to expand...
That is good advice.
At the very least, add an Autonumber field and make that the Primary Key.
You don't even need to do anything once you add it to the table. It is self-populating when new records are added.
 
Upvote 0
Step for creating the new table (each sql command should be run in turn, ignoring the lines with comments):
Code: 
--create the new table
select 
	City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effect_Date, Currency, '' as Rate_Type, 0.00 as Rate
into 
	Freight_Tbl_2
	
--Clear the new table
delete Freight_Tbl_2.*

--STOP: Check all data types in new table for accuracy by opening the table in design view and checking it out.

--Add First Rate
insert into Freight_Tbl_2
select 
	City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effect_Date, Currency, 'Rate_4' as Rate_Type, Rate_4 as Rate
from 
	Freight_Tbl

--Add Second Rate
insert into Freight_Tbl_2
select 
	City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effect_Date, Currency, 'Rate_6' as Rate_Type, Rate_6 as Rate
from 
	Freight_Tbl

--Add Third Rate
insert into Freight_Tbl_2
select 
	City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effect_Date, Currency, 'Rate_BB' as Rate_Type, Rate_BB as Rate
from 
	Freight_Tbl

--Add Fourth Rate
insert into Freight_Tbl_2
select 
	City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effect_Date, Currency, 'Rate_BG' as Rate_Type, Rate_BG as Rate
from 
	Freight_Tbl

--Add Fifth Rate
insert into Freight_Tbl_2
select 
	City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effect_Date, Currency, 'Rate_AA' as Rate_Type, Rate_AA as Rate
from 
	Freight_Tbl

When done, check the counts. The new table should have the same number of records as the old table.
 
Upvote 0
xenou said:
If you don't have a primary key in your table you can expect only pain and suffering from your database.
Click to expand...
Trust me I understand but, I figured it out with the UNION ALL SQL Statement:

SELECT City_Dest, State_Dest, Plant, City_Origin, State_Origin, Effective_Date, Currency, Miles, 'Rate_4' AS Rate_Method, Rate_4 AS Rate FROM Freight_Tbl
UNION ALL
SELECT Etc....to make the new table design...Thanks and again,

I do understand how important normalization is but when they want it on the fly, we sometime have to go back and then normalize and create that Relational DB as intended...Thanks for your help...
 
Upvote 0
Joe4 said:
That is good advice.
At the very least, add an Autonumber field and make that the Primary Key.
You don't even need to do anything once you add it to the table. It is self-populating when new records are added.
Click to expand...
Will do Joe, thanks...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,225,619
Messages
6,186,047
Members
453,335
Latest member
sfd039

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top