MS Access SQL rows to columns

[AccessNoob]

Hello,

I have this table:

Score
ID Accnt No Acctdate Score
1 1 12-Apr-05 760
2 2 12-Apr-05 640
3 3 12-Apr-05 510
4 4 12-Apr-05 200
5 1 12-Mar-05 230
6 2 12-Mar-05 320
7 3 12-Mar-05 450
8 4 12-Mar-05 100
9 1 12-Feb-05 600
10 2 12-Feb-05 550
11 3 12-Feb-05 780
12 4 12-Feb-05 600

and I am trying to get output like this

AcctNo CurrentMonthScore Month-1Score Month-2Score
====== ================= ============ ============

I am stuck at this SQL statement:
SELECT [accnt no],
FROM score
GROUP BY Month([Acctdate])
HAVING (((Month([Acctdate])) In (Month(Now())-1,Month(Now())-2)));

If this is not possible in pure Access SQL, I would be happy to learn SS or Orcle would manage this. If this is already solved here, please send a URL. I was not able to find this in Archives.

Thanks for any help or redirection.
--Paul

 

[PHV]

You may consider a CrossTab query or a PivotTable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244