Skip to main content

AccessTr.neT


Çapraz Tablo Ve Ado

Çapraz Tablo Ve Ado

Çözüldü #1
Aranızda tarih filtreli çapraz tablodan ADO ile veri alabilen oldu mu arkadaşlar
sıkıntı şu ki tarih filtreli olunca alan isimleri sabit olmuyor

https://docs.microsoft.com/en-us/office/...access-sql


Sub TransformX2() 
   
        Dim dbs As Database
        Dim strSQL As String
        Dim qdfTRANSFORM As QueryDef
   
        strSQL = "PARAMETERS prmYear SMALLINT; TRANSFORM " _
            & "Sum(Subtotal) SELECT FirstName & "" """ _
            & "& LastName AS FullName " _
            & "FROM Employees INNER JOIN " _
            & "(Orders INNER JOIN [Order Subtotals] " _
            & "ON Orders.OrderID = " _
            & "[Order Subtotals].OrderID) " _
            & "ON Employees.EmployeeID = " _
            & "Orders.EmployeeID WHERE DatePart" _
            & "(""yyyy"", OrderDate) = [prmYear] "
       
          strSQL = strSQL & "GROUP BY FirstName & "" """ _
            & "& LastName " _
            & "ORDER BY FirstName & "" "" & LastName " _
            & "PIVOT DatePart(""q"",OrderDate)"       
           
        ' Modify this line to include the path to Northwind
        ' on your computer.
        Set dbs = OpenDatabase("Northwind.mdb")
   
        Set qdfTRANSFORM = dbs.CreateQueryDef _
            ("", strSQL)
       
        SQLTRANSFORMOutput qdfTRANSFORM, 1994
       
        dbs.Close
   
    End Sub
   
    Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _
        intYear As Integer)
       
        Dim rstTRANSFORM As Recordset
        Dim fldLoop As Field
        Dim booFirst As Boolean
   
        qdfTemp.PARAMETERS!prmYear = intYear
        Set rstTRANSFORM = qdfTemp.OpenRecordset()
       
        Debug.Print qdfTemp.SQL
        Debug.Print
        Debug.Print , , "Quarter"
   
        With rstTRANSFORM
            booFirst = True
            For Each fldLoop In .Fields
                If booFirst = True Then
                    Debug.Print fldLoop.Name
                    Debug.Print , ;
                    booFirst = False
                Else
                    Debug.Print , fldLoop.Name;
                End If
            Next fldLoop
            Debug.Print
           
            Do While Not .EOF
                booFirst = True
                For Each fldLoop In .Fields
                    If booFirst = True Then
                        Debug.Print fldLoop
                        Debug.Print , ;
                        booFirst = False
                    Else
                        Debug.Print , fldLoop;
                    End If
                Next fldLoop
                Debug.Print
                .MoveNext
            Loop
        End With
       
    End Function


Sub TransformX2() 
        Dim dbs As Database
        Dim strSQL As String
        Dim qdfTRANSFORM As QueryDef
   
        strSQL = "PARAMETERS prmYear SMALLINT; TRANSFORM " _
            & "Sum(Subtotal) SELECT FirstName & "" """ _
            & "& LastName AS FullName " _
            & "FROM Employees INNER JOIN " _
            & "(Orders INNER JOIN [Order Subtotals] " _
            & "ON Orders.OrderID = " _
            & "[Order Subtotals].OrderID) " _
            & "ON Employees.EmployeeID = " _
            & "Orders.EmployeeID WHERE DatePart" _
            & "(""yyyy"", OrderDate) = [prmYear] "
       
          strSQL = strSQL & "GROUP BY FirstName & "" """ _
            & "& LastName " _
            & "ORDER BY FirstName & "" "" & LastName " _
            & "PIVOT DatePart(""q"",OrderDate)"       
           
        ' Modify this line to include the path to Northwind
        ' on your computer.
        Set dbs = OpenDatabase("Northwind.mdb")
   
        Set qdfTRANSFORM = dbs.CreateQueryDef _
            ("", strSQL)
       
        SQLTRANSFORMOutput qdfTRANSFORM, 1994
       
        dbs.Close
   
    End Sub
   
    Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _
        intYear As Integer)
       
        Dim rstTRANSFORM As Recordset
        Dim fldLoop As Field
        Dim booFirst As Boolean
   
        qdfTemp.PARAMETERS!prmYear = intYear
        Set rstTRANSFORM = qdfTemp.OpenRecordset()
       
        Debug.Print qdfTemp.SQL
        Debug.Print
        Debug.Print , , "Quarter"
   
        With rstTRANSFORM
            booFirst = True
            For Each fldLoop In .Fields
                If booFirst = True Then
                    Debug.Print fldLoop.Name
                    Debug.Print , ;
                    booFirst = False
                Else
                    Debug.Print , fldLoop.Name;
                End If
            Next fldLoop
            Debug.Print
           
            Do While Not .EOF
                booFirst = True
                For Each fldLoop In .Fields
                    If booFirst = True Then
                        Debug.Print fldLoop
                        Debug.Print , ;
                        booFirst = False
                    Else
                        Debug.Print , fldLoop;
                    End If
                Next fldLoop
                Debug.Print
                .MoveNext
            Loop
        End With
       
    End Function


Bunu deniyorum ama bir şeyleri eksik yapıyorum galiba
@benbendedeilem
Cevapla

Bir hesap oluşturun veya yorum yapmak için giriş yapın

Yorum yapmak için üye olmanız gerekiyor

ya da

Bu Konudaki Yorumlar
Çapraz Tablo Ve Ado - Yazar: accessman - 09/02/2020, 06:01
Cvp: Çapraz Tablo Ve Ado - Yazar: ozanakkaya - 09/02/2020, 14:03
Cvp: Çapraz Tablo Ve Ado - Yazar: alicimri - 09/02/2020, 14:23
Cvp: Çapraz Tablo Ve Ado - Yazar: feraz - 10/02/2020, 00:51
Cvp: Çapraz Tablo Ve Ado - Yazar: ozanakkaya - 09/02/2020, 14:34
Cvp: Çapraz Tablo Ve Ado - Yazar: alicimri - 10/02/2020, 11:09
Cvp: Çapraz Tablo Ve Ado - Yazar: feraz - 10/02/2020, 11:50
Cvp: Çapraz Tablo Ve Ado - Yazar: accessman - 10/02/2020, 13:28
Task