Tür ve sicile göre en son tarihi bulmak

[tahsinanarat]

@Korhan Ayhan Hocam,
.PivotFields("ADI VE SOYADI").Orientation = xlRowField
.PivotFields("ADI VE SOYADI").Position = 2

koddaki bu kısmı pasif yaptığımda saadece ADI VE SOYADI sutun bilgileri gelmiyor, bunuda düşeyara formülü ile güncel dosyadan tamamlarım, bu şekilde doğru sonuca ulaşmış oluyorum. Emekleriniz için çok teşekkür ediyorum, hakkınızı helal edin, hayırlı hafta sonları diliyorum. Saygılarımla.

 

[Korhan Ayhan]

@tahsinanarat,

SİCİL kısmı tekrar eden bir veri olduğu için DÜŞEYARA ile son tarihe ait ismi getirmeye çalışırken hatalı sonuç alabilirsiniz. Çünkü DÜŞEYARA ilk bulduğu satırı size verecektir. Bu duruma dikkat ederek kullanmanızda fayda var.

 

[Haluk]

Sn. @Haluk Hocam gerçek datalarım olan 117853 satırlık kayıtda 5601 benzersiz kayıttan 4601 kayıt getirdi, yani tam 1000 kayıt kayıp olarak getirdi, özellikle 11497 ile 15373 sicil arasında toplamda 1000 kayıt eksik var, inceliyorum ama diğer kayıtlardan pek bir fark göremedim.

Tahsin Bey;

Bu "eksik kayıt gelmesi" işini ben halen anlayamadım. Veysel Beyin koduyla veriler tam geliyor da, benimkinde eksik mi geliyor? Tekrar deneme şansınız var mı?

.

 

[tahsinanarat]

Sn. @Haluk Hocam, öncelikle çok özür diliyorum, sizin kodları örnek dosyadaki 65536 satırlık excele aldığı kadar yani 65536 satırlık bilgi yükleyip test etmişim. Şimdi 1048000 satırlık excelde test ettiğimde her üç koddada aynı sonucu alıyorum.

Sub TestQuery()
    'Haluk 09/12/2022
    '
    Dim myDB As String, adoCN As Object, strSQL As String, RS As Object
    Dim Sh2 As Worksheet
    Dim i As Integer, mySum As Double
    
    Const adOpenDynamic = 1
    Const adLockOptimistic = 3
    
    myDB = ThisWorkbook.FullName
    Set Sh2 = Sheets("Rapor")
    
    Sh2.Cells.ClearContents
    
    Set adoCon = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.RecordSet")
    
    adoCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 myDB & ";Extended Properties=""Excel 12.0;HDR=Yes"""
    
    strSQL = "Transform Max([TARİH]) " & _
             "Select [SİCİL]  from [Arsiv$] " & _
             "Group by [SİCİL]" & _
             "Pivot [TÜR] "
                          
    RS.Open Source:=strSQL, ActiveConnection:=adoCon, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
    
    For j = 0 To RS.Fields.Count - 1
       Sheets("Rapor").Cells(1, j + 1) = RS.Fields(j).Name
    Next

    Sheets("Rapor").Range("A2").CopyFromRecordset RS
            
    RS.Close
    Set RS = Nothing
    Set adoCon = Nothing
    Set Sh2 = Nothing
End Sub

ADI VE SOYADI sutununu sorgudan çakırttığımda tekrarsız ve doğru olan sonucu elde ediyorum. Adı ve soyadı sutununu ve lazım olan diğer sutunları güncel tuttuğum Peronel Listesinden yine kod ile bir şekilde getirmeyi deneyeceğim.

Yardımlarınız için çok teşekkür ediyorum, mutlu hafta sonları diliyorum.
Saygılar

 

[Haluk]

ADI VE SOYADI sutununu sorgudan çakırttığımda tekrarsız ve doğru olan sonucu elde ediyorum. Adı ve soyadı sutununu ve lazım olan diğer sutunları güncel tuttuğum Peronel Listesinden yine kod ile bir şekilde getirmeyi deneyeceğim.
....
...

Tahsin Bey;

Anladığım kadarıyla; bekar olan kadın personel evlendikten sonra soyadı değişikliği nedeniyle, sizin "Arsiv" sayfasında hem eski soyadıyla hem de yeni soyadıyla aynı sicille yer alıyor.

Bahsettiğiniz gibi personelin sicil numarası ve güncel Ad-Soyad bilgisini içeren ayrı bir tablonuz varsa, bunu daha önceki SQL içeren koda uyarlayarak istenilen sonuçları almak mümkün.

Ben ekli dosyada bir sayfa daha ilave ettim. "GuncelPersonelListesi" isimli bu sayfada, aşağıda belirtildiği gibi personelin güncel isim listesi var.

SİCİL   ADI VE SOYADI
578     Tahsin ANARAT1
1417    TAHSİN ANARAT2222
1578    TAHSİN ANARAT3
2336    TAHSİN ANARAT4
2578    TAHSİN ANARAT5555
6578    TAHSİN ANARAT6
9578    TAHSİN ANARAT77777

Bu listede dikkat edeceğiniz gibi; daha önce 1417 Sicil No'lu TAHSİN ANARAT2 yeni listede TAHSİN ANARAT2222 olarak, 2578 Sicil No'lu TAHSİN ANARAT5 yeni listede TAHSİN ANARAT5555 olarak ve 9578 Sicil No'lu TAHSİN ANARAT7 yeni listede TAHSİN ANARAT77777 olarak güncellenmiş durumdadır.

"Arsiv" isimli sayfadaki mevcut tabloyu ve sözkonusu "GuncelPersonelListesi" isimli sayfadaki yeni tabloyu kullanarak, aşağıdaki ADO-SQL ile istenilen sonuçlar alınmaktadır.

Revize edilmiş dosya da ekte verilmiştir.

Sub TestQuery2()
    'Haluk 11/12/2022
    '
    Dim myDB As String, adoCN As Object, strSQL As String, RS As Object, j As Integer
  
    Const adOpenDynamic = 1
    Const adLockOptimistic = 3
  
    myDB = ThisWorkbook.FullName
    Sheets("Rapor").Cells.ClearContents
  
    Set adoCon = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.RecordSet")
  
    adoCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myDB & ";Extended Properties= 'Excel 12.0; HDR=Yes'"
  
    strSQL = "Transform Format(Max([TARİH]),'dd.mm.yyyy')" & _
             "Select Table1.[SİCİL], " & _
             "Table2.[ADI VE SOYADI] " & _
             "From [Arsiv$] as Table1 " & _
             "Left Join " & _
             "[GuncelPersonelListesi$] As Table2 " & _
             "On Table1.[SİCİL] = Table2.[SİCİL] Where Table1.[SİCİL] = Table2.[SİCİL] " & _
             "Group By Table1.[SİCİL], Table2.[ADI VE SOYADI] " & _
             "Pivot Table1.[TÜR] "
  
    RS.Open Source:=strSQL, ActiveConnection:=adoCon, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
  
    For j = 0 To RS.Fields.Count - 1
       Sheets("Rapor").Cells(1, j + 1) = RS.Fields(j).Name
    Next

    Sheets("Rapor").Range("A2").CopyFromRecordset RS
          
    RS.Close
    Set RS = Nothing
    Set adoCon = Nothing
End Sub

 

[veyselemre]

Sub adoTransform()
    Dim rs As Object, con$, strSQL$, i
    Sheets("Rapor").Select
    Cells.ClearContents
    Set rs = CreateObject("ADODB.Recordset")
 
    con = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & ThisWorkbook.FullName & _
          ";Extended Properties=""Excel 12.0;Hdr=YES"""
      
    strSQL = "TRANSFORM MAX([TARİH]) " & _
             "SELECT [SİCİL], LAST([ADI VE SOYADI]) AS [ADI VE SOYADI] FROM [Arsiv$] " & _
             "GROUP BY [SİCİL] PIVOT [TÜR]"
                
    rs.Open strSQL, con, 1, 1
 
    Range("A2").CopyFromRecordset rs
    
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    rs.Close
    
    With Range("A1").CurrentRegion
        With .Rows(1)
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .Font.Color = vbWhite
            .Interior.Color = 12611584
        End With
        
        With .Offset(1, 2).Resize(.Rows.Count - 1, .Columns.Count - 2)
            .HorizontalAlignment = xlCenter
            .NumberFormat = "dd.MM.yyyy"
        End With
        .EntireColumn.AutoFit
    End With
    
End Sub

 

[tahsinanarat]

Sn. @Haluk Hocam, şimdi indirdiğim çalışmanıza gerçek dataları ilave ederek çalıştırdım, tam istediğim sonucu elde ettim. İlaveten Güncelpersonellistesi sayfasından Cinsiyet ve işe giriş tarihi ve çalıştığı birim gibi sutunları da getirmek istersem;

strSQL = "Transform Format(Max([TARİH]),'dd.mm.yyyy')" & _
             "Select Table1.[SİCİL], " & _
             "Table2.[ADI VE SOYADI] " & _
             "From [Arsiv$] as Table1 " & _
             "Left Join " & _
             "[GuncelPersonelListesi$] As Table2 " & _
             "On Table1.[SİCİL] = Table2.[SİCİL] Where Table1.[SİCİL] = Table2.[SİCİL] " & _
             "Group By Table1.[SİCİL], Table2.[ADI VE SOYADI] " & _
             "Pivot Table1.[TÜR] "

sorguda nasıl bir değişiklik yapmam gerekiyor, Guncelpersonellistesi sayfasındaki başlıklar
Cinsiyet anahtarı
İşe Grş.
Personel Alan
olarak yazılı.
Vakit ayırdığınız için çok teşekkür ederim hocam, Hayırlı günler dilerim.

 

[tahsinanarat]

Sn. @veyselemre hocam, sizin koduda denedim arşiv sayfasındaki aynı sicilli farklı isimleri teke düşürerek doğru olan sonucu getirdi. Emeğinize sağlık, çok teşekkür ederim. Hayırlı günler diliyorum.
Saygılar

 

[Haluk]

Tahsin Bey;

strSQL'i aşağıdakiyle değiştirin;

    strSQL = "Transform Format(Max([TARİH]),'dd.mm.yyyy')" & _
             "Select Table1.[SİCİL], " & _
             "Table2.[ADI VE SOYADI], Table2.[CİNSİYET], Table2.[İŞE GİRİŞ], Table2.[PERSONEL ALAN] " & _
             "From [Arsiv$] as Table1 " & _
             "Left Join " & _
             "[GuncelPersonelListesi$] As Table2 " & _
             "On Table1.[SİCİL] = Table2.[SİCİL] " & _
             "Group By Table1.[SİCİL], Table2.[ADI VE SOYADI], Table2.[CİNSİYET], Table2.[İŞE GİRİŞ], Table2.[PERSONEL ALAN] " & _
             "Pivot Table1.[TÜR] "

Örnek dosya ektedir....

.

 

[tahsinanarat]

Sn. @Haluk hocam, şimdi tam istediğim gibi oldu, Allah Razı Olsun, Elinize zihninize sağlık sayenizle ufkumuz genişliyor.
Saygılarımla.

 

[Haluk]

İşinize yaradığına sevindim. Korhan ve Veysel Beylerin kodları da çok güzel, yararlanmak isteyenler için hepsi güzel kaynak.

.