Rearranging Data in an MS Access 2007 table into a new table format/Result Output

Question

I have Access tables that have the following format:

--------------------------------------
|Name |Math|Science|Reading|Civics|
--------------------------------------
|Mike | A  |  C    |  A    | B    |
|John | B  |  A    |  A    | B    |
|Randy| A  |  C    |  A    | A    |
|Sasha| B  |  C    |  D    | A    |
|Max  | A  |  C    |  A    | A    |
|Jenn | A  |  A    |  A    | C    |
-----------------------------------

Is there any Access Sql code or Access VBA script that I can use so that I can convert (or output in a query/resultset) the above table in the below format?

 --------------------
|   Name      |Grade|
---------------------
|Mike Math    | A   |
|Mike Science | c   | 
|Mike Reading | A   |
|Mike Civics  | B   |
|John Math    | B   |
|John Science | A   | 
|John Reading | A   |
|John Civics  | B   |
|Randy Math   | A   |
 etc...

What I have found so far are the below 2 threads which shows how to bring in all the field names into an array or just basic output using VBA in Access. It's a start for what I am trying to accomplish (since in essence I will need to bring in the field names into the first column). I'll post back if I'm able to figure it out.

https://stackoverflow.com/questions/11548697/how-to-list-fields-name-in-table-in-access-using-sql

http://forums.asp.net/t/1143922.aspx

I'm wondering If I can do something like...

For each fieldname in table (where fieldname != Name)
Begin 
select 
Name + ' ' + Subject as Name, 
(select Grade from table where Name = Name and Subject = Subject) as Grade
End

score 6 · Accepted Answer · answered Jan 30 '14 at 23:49

Yes, it can be done, using UNION ALL:

SELECT [Name] & " Math" AS col1, tab1.Math FROM tab1
UNION ALL
SELECT [Name] & " Science" AS col1, tab1.Science FROM tab1
UNION ALL
SELECT [Name] & " Reading" AS col1, tab1.Reading FROM tab1
UNION ALL
SELECT [Name] & " Civics" AS col1, tab1.Civics FROM tab1
Order BY 1;

Result

Shiva · Answer 2 · 2014-02-06T10:29:50.717

Create a button in a form and paste the below code

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As New ADODB.Recordset

Dim tbl As TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "Result" Then
    db.Execute "DROP TABLE Result"
    Else
    End If
Next tbl

db.Execute "CREATE TABLE Result(Names varchar(100),Grade varchar(10))"

Set db = CurrentDb

rst.Open "Tab1", CurrentProject.Connection, adOpenDynamic

Dim n As Integer
Dim fieldname As String
Dim str As String

For n = 1 To rst.Fields.Count - 1
    fieldname = rst.Fields(n).Name
    str = "Insert Into Result ([Names],Grade) SELECT [Name] & " & Chr(34) & " " & fieldname & Chr(34) & " As Names," & "tab1." & fieldname & " As Grade FROM tab1;"

    db.Execute str


Next n

End Sub

No Matter how many number of fields available, this code will work fine. I have taken help of cha's UNION ALL Query and developed the code. The results will be stored in a new table created by name "Result".

Rearranging Data in an MS Access 2007 table into a new table format/Result Output

2 Answers2