Change the text on the fieldname to record info.

[Guest]

I am trying to change the fieldname on a table into a record text.

e.g the table have 3 columns with the field name: Maths, English, Computer with the exam marks on the record
Maths English Compute
Peter 90 86 9
Mary 60 90 8

I need a table with only 2 columns: Subjects and Marks
Name Subject Math
Peter Maths 9
Peter English 8
Peter Computer 9
Mary Maths 6
Mary English 9
Mary Computer 8

The original [fieldname] Maths, English, computer will be list in the record subject as data
The marks related to each record will be in the next column

How should I design on the query?

 

[Duane Hookom]

You should redesign your table so you don't have 'data values' as field
names. If not possible, you can use a union query to get your desired
results.
SELECT Student, Maths as Mark, "Maths" as Subject
FROM tblStudMarks
UNION ALL
SELECT Student, English, "English"
FROM tblStudMarks
UNION ALL
SELECT Student, Computer, "Computer"
FROM tblStudMarks

--
Duane Hookom
MS Access MVP

I am trying to change the fieldname on a table into a record text.

e.g the table have 3 columns with the field name: Maths, English, Computer

with the exam marks on the record.

Maths English Computer
Peter 90 86 99
Mary 60 90 85

I need a table with only 2 columns: Subjects and Marks.
Name Subject Maths
Peter Maths 90
Peter English 86
Peter Computer 99
Mary Maths 60
Mary English 90
Mary Computer 85

The original [fieldname] Maths, English, computer will be list in the record subject as data.
The marks related to each record will be in the next column.

How should I design on the query?