# Database Discussions > Microsoft Access >  How do I transpose this sample table in MS Access using sql?

## bettereachday

Hello all, 

Is there a way to transpose data from a MS access table using a sql query?

My table records client visits and their scores on those visits. A sample of my table is given below (I inherited the database so I just have to live with the poor normalization :Embarrassment:  ).

tbl.client_tutor

clientID___ Vdate         session    score 
N0010____ 9/7/2006____ 1____ 84 
N0020____ 9/12/2006____ 1____ 92 
N0020____ 2/9/2011_____ 2____ 118 
N0030____ 9/14/2006____ 1____ 95 
N0030____ 12/10/2007____ 2____ 134 
N0030____ 1/15/2009____ 3____ 125 

As you can see one client, N0010 has only one session on 9/7/2006 while another client, N0030 has three sessions.

What I would like to do is transpose the above long and thin table into a short and fat one where the clientID field appears only once as the row identifier on the extreme left and then the session and Vdate repeat in the columns down the table. 

This is the result of the query I'm looking for: 

clientID__session1.Vdate__session1.score__session2  .Vdate__session2.score__session3.Vdate__ session3.score
N0010____ 9/7/2006______84  ___________ NULL _________NULL________NULL____________NULL
N0020____ 9/12/2006_____92___________2/9/2011________118_________NULL____________NULL
N0030____ 9/14/2006_____95___________12/10/2007______134________1/15/2009_________125


Is there anyway to do this in access sql or do I need to do vba code?

Many thanks!

----------


## GolferGuy

> (I inherited the database so I just have to live with the poor normalization).


I'm wondering just how much you know about normalization because the table layout that you have inherited looks fine, but what you want out of the SQL code is normally called a "flat file" which means it is not designed for a relational database.  Normalized tables are designed for a relational database.
OK, now I would like to know what you are after with this "flat file" you desire.  In other words, I would rather help you with the design because that will be much more beneficial down the road, than just giving you some SQL that could end up being a real pain somewhere down the road.
So, what is the outcome that you need from this "flat file?"

----------


## bettereachday

Thanks for the response,

Pardon my sarcasm earlier.

The outcome I'd like from this table (and the other similar tables in my database) is a table containing all clients arranged showing their scores each session in "flat file" form like I described in the thread earlier.

*Background*

I'm responsible for generating a number of scheduled and ad-hoc reports and queries from my organization's small MS access database.

To summarise, the structure of my database is composed of about 20 different tables. The constant fields in all the tables are: client, Vdate and sessionnumber. 

The other fields present vary depending on the table. We administer a number of tests (lets call them testA, testB, testC) and record scores for these tests in seperate long thin tables (like the first table in my thread). My testA table for example  has the following fields:
client, Vdate, sessionnumber,score

Likewise the testB table contains the constant fields plus one or more extra fields eg:
client, Vdate, sessionnumber,score, *clientattitude*, *supervisor*

There is no relationship between any of the tables and any relational information I pull out has to be through queries and joins.

This may be rambling but I wanted to give you as much background as possible.

At the end of the day, I just want to generate one flat file from each table.

Thanks :Smilie:

----------


## GolferGuy

Without know what all the data fields are in the other tables, there is no way I could specify if I feel the database is truly normalized, but from what little you have shared (which has been enough) it seems like someone normalized it too much. That is, went beyond the rules of normalization and just split EVERYTHING out into it's own table.  
Anyway, to make what could be a long story, short:  I personally do not know of any way to take this data and turn it into a flat file with SQL.  In some cases it can be done, but in your case, I don't know of any way to do it.  That means, it would be a job for VBA.
Sorry,

----------


## Allan Murphy

Using VBA you would need to store your input data (SQL) into an array, then manipluate the data and store it into an output array in the format required, then write the output array to a table.

----------


## rdub

See if this Sql Gets it done for you.  I am guessing at the Table Name and Column Names.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

SELECT ClientID, Max(Vdate1) AS Session1Vdate, 
     Max(Score1) AS Session1Score, 
     Max(Vdate2) AS Session2Vdate, 
     Max(Score2) AS Session2Score, 
     Max(Vdate3) AS Session3Vdate, 
     Max(Score3) AS Session3Score
FROM (
     SELECT ClientID, Vdate AS Vdate1, 
                Score AS Score1,  
                '' AS Vdate2, '' AS Score2,  
                '' AS Vdate3, '' AS Score3
      FROM ClientTutor
      WHERE Session=1
      Union
      SELECT  ClientID, '', '', 
                  Vdate, Score, 
                  '', ''
     FROM ClientTutor
     WHERE Session=2
     UNION SELECT  ClientID, '', '', 
                           '', '',  
                           Vdate, Score
     FROM ClientTutor
     WHERE Session=3
) as qClientTutor
GROUP BY qClientTutor.ClientID

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Rdub

----------

