Change of table structure via Queries(?) 3

[kingz2000]

Hi Guys,

I need your help regarding Tables in Database, in general. Its been a while since I was here, and had a Database problem since I haven'*t been working with DBs at all..So bear wih me

I have a table structured with the columns Contract, Section, Amount 2014, Amount 2015, Amount 2016
what I'd like is to change the structure so that the table columns are Contract, Section, Amount, Year.

so data reading like this W234 ,1 , 50, 60 , 55 will read
W234, 1, 50,2014
W234, 1, 60,2015
W234, 1 , 55,2016

so from i sentence i should have 3.
How do i do this? It would be great if I could accomplish this with a query or two.

Cheers for all your help in advance.

 

[SkipVought]

HI,

Can be done constructing several queries.

But here's a quick way in Excel for a one time solution without queries. Check out FAQ68-5287. Import your table data into Excel and proceed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Try this:
[tt]
Select Contract, Section, [Amount 2014] As Amount, 2014 As [Year]
From YourTable
UNION
Select Contract, Section, [Amount 2015], 2015
From YourTable
UNION
Select Contract, Section, [Amount 2016], 2016
From YourTable
[/tt]

BTW, Names with Spaces and reserved words are bad choices for field names in a table.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[kingz2000]

Thanks Andrzejek..It worked perfectly