Normalizing the table in ms access

[sivani_007]

Hello there,

Wondering if anyone can please help me to figure out how i can normalized this data which has been imported from excel to ms access.

Channel
IslandsMalta UAE JordanSingapore
0% 0% 0% 0% 0%
0% 4% 5% 5% 5%
0% 12% 6% 6% 6%
0% 12% 10% 10% 10%
9% 12% 12% 12% 12%
12% 12% 12% 12% 12%
13% 11% 10% 10% 10%
14% 11% 10% 10% 10%
14% 9% 10% 10% 10%
14% 9% 6% 6% 6%
13% 3% 6% 6% 6%
7% 2% 5% 5% 5%
4% 2% 4% 4% 4%
0% 2% 4% 4% 4%

into this ...

Regions percentages
channel islands 0%
channel islands 0%
channel islands 0%
channel islands 0%
channel islands 9%
channel islands 12%
channel islands 13%
channel islands 14%
channel islands 14%
channel islands 14%
channel islands 13%
channel islands 7%
channel islands 4%
channel islands 0%
Malta 0%
Malta 4%
Malta 12%
Malta 12%
Malta 12%
Malta 12%
Malta 11%
Malta 11%
Malta 9%
Malta 9%
Malta 3%
Malta 2%
Malta 2%
Malta 2%

====and so on ====
can Some one out there please help me to do this via sql or vb programming .

Many thanks for your help

 

[spikepl]

Copy/pase columns in excel - that's the simplest.

Otherwise, construct an append query that grabs a column from one table and appends it to another table. Repeat for each column. Start with just selecting data for one column. Play with it in the query designer. Once that works, change the query type from SELECT to APPEND.

 

[namliam]

fastest way is to make a few append queries, one query per column where you "hard code" the first column and fill the second
i.e.
Select "Malta", [Malta]
from [yourtable]

 

[sivani_007]

hi there,

Many thanks for your input i will have a go at append query and see how it goes..

 

[sivani_007]

hi there,,

it would be great if someone could give me step by step on how to create append query on this as i am very basic in ms access.

thanks

 

[namliam]

You tried for a full 10 seconds?

Click create
Click query design
add your table
click on the button "Append" (it has the plus sign on it)
Type "Malta" (as an example) into the first column
add the column Malta from your table to the query (double click on the field in the table or drag it)
Now save the query and you are happy

 

[BeeJayEff]

how i can normalized this data

Well I really don't understand what you mean by this. What are you trying to do ? Do you already have an Access database with appropriate tables ?

What you have already shown us can be very simply achieved within Excel just by transposing rows and columns. What more do you want ?

 

[sivani_007]

hi there,

I have now solved the problem i have copied the labels in excel and created the append query to append individual region to a main table and i have got the result i wanted..

Works just like i wanted..

thank you all for your inputs..