access sql problem

[rizunb]

hey guys
here's a question
I have a table with 10 fields in it
What I want to do is to have these fields names as data in a single column

example is here
lets say I have a table with such data

StudentID Course Hours A B C D E F
222 3913 4 2 1 2 2 1 2
223 3813 3 1 2 2 2 1 2

I want to change it to some thing like this
StudentID Course Hours value
222 3913 4 A
222 3913 4 B
222 3913 4 C
222 3913 4 D
222 3913 4 E
222 3913 4 F

 

[jrbarnett]

You will need to create a new table and then run a series of insert queries to copy across the data, thus:

insert into newtable (studentid, course, hours, value)
select studentid, course, hours, a
from table

then

insert into newtable (studentid, course, hours, value)
select studentid, course, hours, b
from table

etc, one for each of the "value" fields that you are moving across.

Be sure to set the primary key on the new table so that such values will be permitted or the insert queries will fail.

John

 

[lespaul]

you don't need a new table, you just need a query that structures your data and then use that query as your source:


SELECT StudentID, Course, Hours, "A" FROM TableName
UNION
SELECT StudentID, Course, Hours, "B" FROM TableName
UNION
SELECT StudentID, Course, Hours, "C" FROM TableName


etc.

Now, you don't show anything in your wanted results that includes the VALUE of A, B, C, etc. (these numbers:2 1 2 2 1 2)

HTH

leslie