TRANSFORM First(r.RaporEkibi) AS FirstOfRaporEkibi
SELECT r.Sira
FROM  
(SELECT q.RaporTuru, q.RaporEkibi, (SELECT COUNT(*)
        FROM (SELECT DISTINCT tbl_Orders.RaporTuru, tbl_Orders.RaporEkibi
FROM tbl_Orders
WHERE tbl_Orders.Rapor="x"
ORDER BY tbl_Orders.RaporTuru, tbl_Orders.RaporEkibi) AS q2
        WHERE q2.RaporTuru = q.RaporTuru
          AND q2.RaporEkibi < q.RaporEkibi
    )+1 AS Sira
FROM (SELECT DISTINCT tbl_Orders.RaporTuru, tbl_Orders.RaporEkibi
FROM tbl_Orders
WHERE tbl_Orders.Rapor="x"
ORDER BY tbl_Orders.RaporTuru, tbl_Orders.RaporEkibi) AS q
ORDER BY q.RaporTuru, q.RaporEkibi) AS r
GROUP BY r.Sira
PIVOT r.RaporTuru In ("Kdv İade","Bağımsız Denetim","Tam Tasdik","Diğer Raporlar");

SELECT DISTINCT tbl_Orders.RaporTuru, tbl_Orders.RaporEkibi
FROM tbl_Orders
WHERE tbl_Orders.Rapor="x"
ORDER BY tbl_Orders.RaporTuru, tbl_Orders.RaporEkibi;

SELECT q.RaporTuru, q.RaporEkibi, (SELECT COUNT(*)
        FROM qryDistinctRaporEkibi AS q2
        WHERE q2.RaporTuru = q.RaporTuru
          AND q2.RaporEkibi < q.RaporEkibi
    )+1 AS Sira
FROM qryDistinctRaporEkibi AS q
ORDER BY q.RaporTuru, q.RaporEkibi;

TRANSFORM First(r.RaporEkibi) AS FirstOfRaporEkibi
SELECT r.Sira
FROM qryRankedRaporEkibi AS r
GROUP BY r.Sira
PIVOT r.RaporTuru In ("Kdv İade","Bağımsız Denetim","Tam Tasdik","Diğer Raporlar");

TRANSFORM First(r.RaporEkibi) AS EkipAd
SELECT r.Sira
FROM (
	  SELECT q.RaporTuru, q.RaporEkibi, (
										 SELECT COUNT(*)         
										 FROM tbl_Orders AS q2         
										 WHERE q2.RaporTuru = q.RaporTuru           
										   AND q2.RaporEkibi < q.RaporEkibi           
										   AND q2.Rapor="x"     )+1 AS Sira 
	  FROM tbl_Orders AS q WHERE (((q.Rapor) = "x")) 
	 )  AS r
GROUP BY r.Sira
PIVOT r.RaporTuru IN ('Kdv İade','Bağımsız Denetim','Tam Tasdik','Diğer Raporlar');

Sub DinamikPivotGuncelleYarat()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xSQL As String
    Dim pivotFields As String
    
    Set db = CurrentDb()
     xSQL = "SELECT RaporTuru " & _
            "FROM tbl_Orders where  (((Rapor)=""x"")) " & _
            "GROUP BY RaporTuru " & _
            "ORDER BY Count(RaporEkibi) DESC;"

    Set rs = db.OpenRecordset(xSQL)
    
    ' PIVOT için kullanılacak sütunları oluştur
    Do While Not rs.EOF
        pivotFields = pivotFields & "'" & rs(0) & "',"
        rs.MoveNext
    Loop
    
    If Len(pivotFields) > 0 Then pivotFields = Left(pivotFields, Len(pivotFields) - 1)
    
    ' sorgudaki PIVOT kısmı güüncellemek için
     xSQL = "TRANSFORM First(r.RaporEkibi) AS EkipAd " & _
            "SELECT r.Sira " & _
            "FROM ( " & _
            "SELECT q.RaporTuru, q.RaporEkibi, (SELECT COUNT(*) " & _
            "        FROM tbl_Orders AS q2 " & _
            "        WHERE q2.RaporTuru = q.RaporTuru " & _
            "          AND q2.RaporEkibi < q.RaporEkibi " & _
            "          AND q2.Rapor=""x"" " & _
            "    )+1 AS Sira " & _
            "FROM tbl_Orders AS q " & _
            "WHERE (((q.Rapor) = ""x"")) " & _
            ") AS r " & _
            "GROUP BY r.Sira " & _
            "PIVOT r.RaporTuru IN (" & pivotFields & ");"
    
    On Error Resume Next
        db.QueryDefs.Delete "SorguAdınız"
    On Error GoTo 0

    ' Yeni sorgu oluştur
    Dim qdf As DAO.QueryDef
    Set qdf = db.CreateQueryDef("SorguAdınız", xSQL)
End Sub