INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Change of table structure via Queries(?)

Change of table structure via Queries(?)

(OP)
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.

RE: Change of table structure via Queries(?)

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: NORMALIZE Your Table using the PivotTable Wizard. Import your table data into Excel and proceed.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Change of table structure via Queries(?)

2
Try this:

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


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.

RE: Change of table structure via Queries(?)

(OP)
Thanks Andrzejek..It worked perfectly:)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close