Rows to extra column sql

[andy49]

I have a table which holds:
Idnumber (autonumber)
Staffid (integer)
classID (integer)

Basically class 18 is taught by staff member 14


Eg
Staffid classid
14 18
13 18
12 17
2 11

I am hoping to run a query to convert the data to

ClassID
STAFF1IF
STAFF2ID




Class 18 has first teacher 14 and second teacher 13 ( order not important)
Classid staff1id staff2id
18 14 13
17 12
11 2

Can anyone help?

[June7]

Options:

1. SQL like:

TRANSFORM Max(StaffID) AS MaxStaffID
SELECT ClassID
FROM Table1
GROUP BY ClassID
PIVOT "Staff" & DCount("*","Table1","ClassID=" & [ClassID] & " AND IDnumber<" & [IDnumber])+1;

2. VBA - review http://allenbrowne.com/func-concat.html

[andy49]

Thank you so much. This worked brilliantly.