3

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
11
  • And why are you planning to store 2 different pieces of information (name and subject) in the same column? Why not 3 columns, name, subject, grade? Jan 26, 2014 at 23:10
  • Using the example I gave above we don't wan't "Mike", "Mike", "Mike" several times duplicated in "Name" column. In my actual DB table, the "Name" column is really going to be an Item column (for hotel items) and we can't have duplicate Items. For example we want "Cup-King", "Cup-Queen", "Cup-Junior" instead of "cup", "cup", "cup" in the Item field with the room type (King, Queen, Junior) in another column.
    – Juan Velez
    Jan 27, 2014 at 4:21
  • 1
    I suggest you edit the question and clarify: Do all the tables have the same exact format (number of columns and names of columns)? Should the answer be for Access or SQL-Server? Jan 28, 2014 at 9:11
  • 1
    Otherwise does TRANSFORM help you? Jan 30, 2014 at 17:05
  • 1
    I don't pivot/unpivot very often and each time I have to read the documentation and examples thoroughly to determine what I want. Good luck! If TRANSFORM doesn't work, it can be done with plain SQL specially written for each case, but those statements could be generated. Jan 30, 2014 at 17:25

2 Answers 2

6
+50

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

1
  • Works Great! Since I have about 50 tables I'm going to try to have a vba or sql script output the code for me for all the different tables by bringing the column names (since they are different) but the code you have provided me will save me A TON of TIME! Thank you.
    – Juan Velez
    Feb 5, 2014 at 21:32
1

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".

1
  • Thank you! I had already used the previous answer to convert my tables but I was able to use your code to Union all 50 tables without having to write out all the code manually! Save me a lot of time! Thankas again!
    – Juan Velez
    Feb 16, 2014 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.