Table Normalization Help

[Mac1206]

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

City_Dest	State_Dest	Plant	City_Origin	State_Origin	Efffect_Date	Currency	Rate_4	Rate_6	Rate_BB	Rate_BG	Rate_AA
Oxon Hill	Maryland,US	US-MA-Richmond	Kaleen	Texas,US	5/1/2015	USD	55.12	23.21	45.6	0	0
Oxon Hill	Maryland,US	US-MA-Seattle	Washington	Seattle,US	5/2/2015	USD	48.5	26	13.55	0	0
Oxon Hill	Maryland,US	US-MA-Petersburg	Petersburg	Virginia,US	5/3/2015	USD	35.15	32.58	14.5	0	0
Oxon Hill	Maryland,US	US-MA-Ft. Dix	Ft. Dix	New Jersey,US	5/4/2015	USD	45.68	21.5	32.6	0	0

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Result_Tbl needed....

City_Dest	State_Dest	Plant	City_Origin	State_Origin	Efffect_Date	Currency	Rate_Type	Rate
Oxon Hill	Maryland,US	US-MA-Richmond	Kaleen	Texas,US	5/1/2015	USD	Rate_4	55.12
Oxon Hill	Maryland,US	US-MA-Seattle	Washington	Seattle,US	5/2/2015	USD	Rate_BB	13.55
Oxon Hill	Maryland,US	US-MA-Petersburg	Petersburg	Virginia,US	5/3/2015	USD	Rate_6	32.58
Oxon Hill	Maryland,US	US-MA-Ft. Dix	Ft. Dix	New Jersey,US	5/4/2015	USD	Rate_BG	0

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[xenou]

Hi, you will need to identify the column(s) for the primary key of the proposed table first.

 

[Mac1206]

Hi, you will need to identify the column(s) for the primary key of the proposed table first.

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?

 

[Mac1206]

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...

 

[xenou]

If you don't have a primary key in your table you can expect only pain and suffering from your database.

 

[Joe4]

If you don't have a primary key in your table you can expect only pain and suffering from your database.

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.

 

[xenou]

Step for creating the new table (each sql command should be run in turn, ignoring the lines with comments):

--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.

 

[Mac1206]

If you don't have a primary key in your table you can expect only pain and suffering from your database.

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...

 

[Mac1206]

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.

Will do Joe, thanks...