CODEWITHSUNDEEP
sqlms-access
tee
tee

Reputation: 1336

SQL using TRANSFORM to reformat my table in MS-Access

I need help reformatting my table using TRANSFORM. I've tried a few things but for some reason it just won't work. Here's an example of what I have to do. Say I have this table:

     table1:

| in/out | year   | 
|   in   | 2011-12|
|   in   | 2011-12|
|   in   | 2012-13|
|   out  | 2011-12|
|   out  |2012-13 |
|   out  |2012-13 |

I want to reformat this table to look like this:

|in/out| 2011-12 | 2012-13 
|  in  |   2     |   1
|  out |   1     |   2

The first column is the in/out and the rest are just the years where the numbers under them is just a count of for example how many ins or outs happened that year. Any help would be greatly appreciated. Thanks in advance.

Upvotes: 1

Views: 29

Answers (2)

tee
tee

Reputation: 1336

I found the solution. It's this:

Transform Count(*)
SELECT [in/out] FROM table1 GROUP BY [in/out]
PIVOT year IN("2011-12", "2012-13");

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

The query would be 

TRANSFORM   Sum(amount) AS SumOfamount
SELECT      [in/out]
FROM        Table1
GROUP BY    [in/out]
PIVOT       [year]

Graphically it looks like this:

enter image description here

You might want to change the field name from year though as that's a reserved word and may confuse the database.

Upvotes: 1

Related Questions