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
  • 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? – ypercubeᵀᴹ Jan 26 '14 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 '14 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? – ypercubeᵀᴹ Jan 28 '14 at 9:11
  • 1
    Otherwise does TRANSFORM help you? – Colin 't Hart Jan 30 '14 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. – Colin 't Hart Jan 30 '14 at 17:25
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

|improve this answer|||||
  • 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 '14 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".

|improve this answer|||||
  • 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 '14 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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