tek Sorgu halinde
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');
sorgu oluşturma/güncelleme kodu
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
