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

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

[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