Sort of transposing a table ????

[ksor]

I have a table with records looking likge this:





How can I change it to look like this:

BarnID ForælderID1 ForælderID2 ForælderID 3 .... (max 5)
26 52 53 107
27 69 70
28 26 27
29 28 31

ect.

Using VBA or SQL in VBA code ?

There will be from NONE to 5 ID's pr. BarnID.

[orange]

This may get you started.
I created a table tblBarns (I added MyID as PK)

barnid	ForID	myiD
26	52	1
26	53	2
26	107	3
27	69	4
27	70	5
28	26	6
28	27	7
29	28	8
29	31	9

Sub ksor1()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Dim sqlF As String
    Dim sqlB As String
    Dim sOut As String
10  sqlB = "Select distinct BarnID from tblBarns"
20  sqlF = "Select ForID from tblBarns where BarnID ="

30  Set db = CurrentDb
40  Set rs1 = db.OpenRecordset(sqlB)
50  Do While Not rs1.EOF
60      sOut = rs1!Barnid & " "
70      Set rs2 = db.OpenRecordset(sqlF & rs1!Barnid)
80      Do While Not rs2.EOF
90          sOut = sOut & rs2!ForID & " "
100         rs2.MoveNext
110     Loop
120     Debug.Print sOut
130     rs1.MoveNext
140 Loop
End Sub

Result:

26 52 53 107 
27 69 70 
28 26 27 
29 28 31

[ksor]

THX ... I'll try to get some ideas from it !

[June7]

Also try:

TRANSFORM First(tblBarns.ForID) AS FirstOfForID
SELECT tblBarns.barnID
FROM tblBarns
GROUP BY tblBarns.barnID
PIVOT DCount("*","tblBarns","barnID=" & [barnID] & " AND myID<" & [myID])+1;