Accesden bakıye sorgusu

Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
iyi akşamlar,
Access sorgusunda "over partıtıon" kodunu kullanamdığım için farklı bir sorgu kullanımfakat olması gereken bakiyeyi getiremedim
Bu konuda cok açılmış başlık var ama cevabımı bulamadım

Dosya ektedir.
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
Ne yaptımsa olmadı :(
Sorguda toplam sürekli hatalı olarak ilerliyor.
Acaba sorgu sonucunu döngüye alıp orada mı toplama işlemi yapsam?
 

Zeki Gürsoy

Uzman
Uzman
Katılım
31 Aralık 2005
Mesajlar
4,225
Excel Vers. ve Dili
Office 2019 (64 bit) - Türkçe
Access için yürüyen bakiye uygulamalarında sub-query hem zahmetli, hem de pek ideal çözüm olduğu söylenemez. Bunun iki nedeni var:

Birincisi, outer ifadenin her kaydı için, tüm kayıtlarda yeni inner query (sum) çalışacağı için sorgu performansı oldukça düşecektir.
İkincisi, kayıtları birbirinden ayıran tekil bir alan olmak zorunda. Bu zaten örnek dosyanızda var; ancak sıralaması tarih sırasından farklı ise işi zorlaştırmakta.

Buna göre, bir nakli yekün bakiyesi hesaplatarak tarih aralığını döngü ile üzerine kümlatif bakiye hesaplattırmak en kolay ve kestirme çözüm olur. Bu, MS Access için geçerli. Sql Server türü veritabanları zaten kendi içerisinde cursor ve değişken kullanmaya izin verdiği için Excel tarafında ilave iş yaptırmaya gerek kalmıyor.

Kolay gelsin.


.
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
Access için yürüyen bakiye uygulamalarında sub-query hem zahmetli, hem de pek ideal çözüm olduğu söylenemez. Bunun iki nedeni var:

Birincisi, outer ifadenin her kaydı için, tüm kayıtlarda yeni inner query (sum) çalışacağı için sorgu performansı oldukça düşecektir.
İkincisi, kayıtları birbirinden ayıran tekil bir alan olmak zorunda. Bu zaten örnek dosyanızda var; ancak sıralaması tarih sırasından farklı ise işi zorlaştırmakta.

Buna göre, bir nakli yekün bakiyesi hesaplatarak tarih aralığını döngü ile üzerine kümlatif bakiye hesaplattırmak en kolay ve kestirme çözüm olur. Bu, MS Access için geçerli. Sql Server türü veritabanları zaten kendi içerisinde cursor ve değişken kullanmaya izin verdiği için Excel tarafında ilave iş yaptırmaya gerek kalmıyor.

Kolay gelsin.


.
Dün neredeyse bir günü bu işe ayırdım yüzlerce yazı okudum ama haliyle aynı yerdeyim, Normalde oracledan veri çekerken kendi sorgusu var
SUM(SalePrice) OVER(PARTITION BY OrderCategory order BY orderDate)
aynı sorgu excelde ya da vb.net de çalışmıyor. Muhtemelen de dediğiniz gibi çalışmayacak.
An itibariyle sorgu içerisinde toplam alma uğraşımı bırakıyorum..

@Zeki Gürsoy bey, mevcut sorguyu döngüye aldığımızı varsayarsak her satır sorgusu için toplam alma bir önderiniz var mı?
birden fazla cari de olacaktır.

Bir cari bitip diğer cari başladığında o cariye ait toplama işlemi yapmalı. Kurguyu kafamda oluşturamadım. Bir iki deneme yaptım ama başarılı olamadım

Saygılar
 

Zeki Gürsoy

Uzman
Uzman
Katılım
31 Aralık 2005
Mesajlar
4,225
Excel Vers. ve Dili
Office 2019 (64 bit) - Türkçe
Sql Server sistemlerde olan Over/Partition ifadesi tam olarak bu işi kendisi yapabiliyor. Excel tarafında bence iki recordset açarak ilki cari listesini, diğeri de hareketleri önceki mesajımda belirttiğim gibi hesaplasın. İç içe geçmiş sorgulardan daha kolay olacağını düşünüyorum.

.
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
@Zeki Gürsoy bey rica etsem dosya üzerinden bir örneklem yapabilir misiniz?
Dünden beri aklım çorba oldu gerçekten ,Şu an neyi neyle kurgulayacağımı bilemiyorum :)
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
Sql Server sistemlerde olan Over/Partition ifadesi tam olarak bu işi kendisi yapabiliyor. Excel tarafında bence iki recordset açarak ilki cari listesini, diğeri de hareketleri önceki mesajımda belirttiğim gibi hesaplasın. İç içe geçmiş sorgulardan daha kolay olacağını düşünüyorum.

.
Kod:
Sub TÜMVERİLER()
Cells.ClearContents
Set Con = CreateObject("Adodb.Connection")
    Set RS = CreateObject("Adodb.RecordSet")
yol = ThisWorkbook.Path & "\Veritabanı.mdb"
Con.Open "provider=microsoft.ace.oledb.12.0;data source=" & yol & ""
   SORGU = " SELECT A.CARKOD, A.TARIH,A.KAYITNO,A.BA,A.ISLTARIH, IIF(A.BA='B',A.TUTAR, 0) AS BORC, IIF(A.BA='A',A.TUTAR*-1, 0) AS ALACAK ,'' AS BAKIYE" & _
   " FROM CARTH001 A  ORDER BY A.CARKOD,A.TARIH ASC  "

       RS.Open SORGU, Con, 1, 3
        If RS.RecordCount <> 0 Then
        For X = 1 To RS.Fields.Count
      Cells(1, X) = RS.Fields(X - 1).Name
   Next
   Do While Not RS.EOF
           Row = Row + 1
           sonstn = RS.Fields.Count
           For Findex = 0 To sonstn - 1
          Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
        B = Cells(Row + 1, sonstn - 2)
        A = Cells(Row + 1, sonstn - 1)
          If Cells(Row + 1, 1) <> "" Then
          
          If Row = 1 Then Cells(Row + 1, sonstn) = B + A
         If Cells(Row + 1, 1) = Cells(Row, 1) Then
          topl = Cells(Row, sonstn)
          If Row > 1 Then
          Cells(Row + 1, sonstn) = topl + A + B
             End If
             Else
             Cells(Row + 1, sonstn) = A + B
             End If
          End If
         Next Findex
           RS.MoveNext
        Loop
'Range("A2").CopyFromRecordset rs
               RS.Close: Con.Close
Set Con = Nothing: Set RS = Nothing: SORGU = ""
Else
Exit Sub
End If
 
End Sub
Ne kadar sağlıklı ve hızlı olur bilmiyorum ama artık bu şekilde bir kod yazdım. Tavsiyelerinizi bekliyorum:)
 

Haluk

𐱅𐰇𐰼𐰚
Katılım
7 Temmuz 2004
Mesajlar
12,270
Excel Vers. ve Dili
64 Bit 2010 - İngilizce
+
Google Sheets
+
JScript
En azından doğru çalışıyor gibi .... ;)

.
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
Kodlarda biraz değişiklikler yaptım sütunlarında yerleri değişebilir düşüncesiyele sutunları değişkene atadım,borçbakiye ve alacakbakiye ekledim.
Ek olarak her cari değişikliğinde alt toplam aldım.Fakat burada ek son carinin toplamını aldıramadım.
Alternatif kod öneriniz varsa paylaşabilirmisiniz



Kod:
Sub TÜMVERİLER()
Cells.Clear
Set Con = CreateObject("Adodb.Connection")
    Set RS = CreateObject("Adodb.RecordSet")
 yol = ThisWorkbook.Path & "\Veritabanı.mdb"
 
Con.Open "provider=microsoft.ace.oledb.12.0;data source=" & yol & ""
UNVAN = "SELECT UNVAN FROM CARTM001 WHERE CARKOD=A.CARKOD"
   SORGU1 = " SELECT A.CARKOD,(" & UNVAN & ") AS UNVAN, A.TARIH,A.ISLEMTIPI,A.KAYITNO,A.BA,A.ISLTARIH, IIF(A.BA='B',A.TUTAR, 0) AS BORC, IIF(A.BA='A',A.TUTAR, 0) AS ALACAK ,'' AS BORCBAKIYE,'' AS ALACAKBAKIYE" & _
   " FROM CARTH001 A  ORDER BY A.CARKOD,A.TARIH ASC  "
   sorgu = "SELECT TARIH,CARKOD,UNVAN,ISLEMTIPI,BORC,ALACAK,BORCBAKIYE,ALACAKBAKIYE,'' AS BAKIYE FROM (" & SORGU1 & ")"
    TOPA = 0
     TOPB = 0
        RS.Open sorgu, Con, 1, 3
        If RS.RecordCount <> 0 Then
        For X = 1 To RS.Fields.Count
      Cells(1, X) = RS.Fields(X - 1).Name
    If RS.Fields(X - 1).Name = "BORC" Then STN_B = X
    If RS.Fields(X - 1).Name = "ALACAK" Then STN_A = X
      If RS.Fields(X - 1).Name = "BAKIYE" Then STN_BKY = X
    If RS.Fields(X - 1).Name = "BORCBAKIYE" Then STN_BBKY = X
    If RS.Fields(X - 1).Name = "ALACAKBAKIYE" Then STN_ABKY = X
     If RS.Fields(X - 1).Name = "CARKOD" Then STN_CR = X
    
   Next
    Do While Not RS.EOF
           Row = Row + 1
           For Findex = 0 To RS.Fields.Count - 1
       Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
        B = Cells(Row + 1, STN_B)
        A = Cells(Row + 1, STN_A)
         If Row = 1 Then Cells(Row + 1, STN_BKY) = A - B
        
         BKY = Cells(Row, STN_BKY)
         If Cells(Row + 1, 1) <> "" Then
         If Cells(Row + 1, STN_CR) = Cells(Row, STN_CR) Then
          Cells(Row + 1, STN_BKY) = BKY + (A - B)
             Else
            
             Cells(Row + 1, STN_BKY) = A - B
             End If
          End If
         Next Findex
         If Cells(Row + 1, STN_BKY) < 0 Then
         Cells(Row + 1, STN_BBKY) = Abs(Cells(Row + 1, STN_BKY))
         Else
         Cells(Row + 1, STN_ABKY) = Cells(Row + 1, STN_BKY)
         End If
      
         If Row > 1 Then
          TOPA = Cells(Row, STN_A) + TOPA
         TOPB = Cells(Row, STN_B) + TOPB
           If Cells(Row, STN_CR) <> Cells(Row + 1, STN_CR) Then
            Rows(Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove: Row = Row + 1
            
            
          Cells(Row, 1) = "TOPLAM"
           Rows(Row).Font.Bold = True
          Cells(Row, STN_B) = TOPB: TOPB = 0
          Cells(Row, STN_A) = TOPA: TOPA = 0
          Cells(Row, STN_BBKY) = Cells(Row - 1, STN_BBKY)
          Cells(Row, STN_ABKY) = Cells(Row - 1, STN_ABKY)
           Rows(Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove: Row = Row + 1
            End If: End If
           RS.MoveNext
        Loop
        
       Columns(STN_BKY).ClearContents
      
'Range("A2").CopyFromRecordset rs
               RS.Close: Con.Close
Set Con = Nothing: Set RS = Nothing: sorgu = ""
Else
Exit Sub
End If
End Sub
 

Haluk

𐱅𐰇𐰼𐰚
Katılım
7 Temmuz 2004
Mesajlar
12,270
Excel Vers. ve Dili
64 Bit 2010 - İngilizce
+
Google Sheets
+
JScript
Aslında Cari hesap kodlarını aynı MDB dosyasında ayrı ayrı tablolarda hareketlerini takip etseniz, istediğiniz bir Cari kodun günlük bakiyelerini daha rahat bir şekilde Excel'de listelersiniz, ama siz bilirsiniz tabii ...

.
 

Haluk

𐱅𐰇𐰼𐰚
Katılım
7 Temmuz 2004
Mesajlar
12,270
Excel Vers. ve Dili
64 Bit 2010 - İngilizce
+
Google Sheets
+
JScript
7.mesajınızdaki koda alternatif, biraz daha hızlı dosya ektedir...

.
 

Ekli dosyalar

Son düzenleme:

Zeki Gürsoy

Uzman
Uzman
Katılım
31 Aralık 2005
Mesajlar
4,225
Excel Vers. ve Dili
Office 2019 (64 bit) - Türkçe
OVER ifadesi sql server veritabanı sistemlerine (ms, oracle.. vs) has bir özelliktir. Bilgisayarımda kurulu olan MS Sql server sistemine ilgili Access dosyasını linked server olarak tanıtarak aşağıdaki listeyi kolayca alabildim. Oracle'da nasıl kullanılır hiç bilmiyorum ama, ilgilenirseniz MS Sql Server için Linked Server konusu ile OVER kullanarak çözüme ulaşabiliyorsunuz.

SQL:
SELECT carkod,tarıh, ıslemtıpı,acıklama, ba, tutar,
sum(case ba when 'B' then tutar else -tutar end) over(partition by carkod order by tarıh, KAYITNO) as Bakiye
FROM OPENQUERY(myserver2, 'select * from [CARTH001]')
order by carkod, tarıh
215588


.
 

Haluk

𐱅𐰇𐰼𐰚
Katılım
7 Temmuz 2004
Mesajlar
12,270
Excel Vers. ve Dili
64 Bit 2010 - İngilizce
+
Google Sheets
+
JScript
Zeki Bey, güzel olmuş doğrusu ..... Bahsettiğin MS Sql server sistemine Excel dosyasını da tanıtıp aynı işi yapabiliyor muyuz?

.
 

Zeki Gürsoy

Uzman
Uzman
Katılım
31 Aralık 2005
Mesajlar
4,225
Excel Vers. ve Dili
Office 2019 (64 bit) - Türkçe

Haluk

𐱅𐰇𐰼𐰚
Katılım
7 Temmuz 2004
Mesajlar
12,270
Excel Vers. ve Dili
64 Bit 2010 - İngilizce
+
Google Sheets
+
JScript
Teşekkürler Üstad, inceleyecek yeni bir konu çıktı .... ;)

.
 

Zeki Gürsoy

Uzman
Uzman
Katılım
31 Aralık 2005
Mesajlar
4,225
Excel Vers. ve Dili
Office 2019 (64 bit) - Türkçe
İyi o zaman... Visual Studio'dan sonra Sql Server macerasına başlayalım artık. :)
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
7.mesajınızdaki koda alternatif, biraz daha hızlı dosya ektedir...

.
Cevap verildiği ile ilgili mesaj gelmedi geç gördüm mesajları kusura bakmayın.
Bugün foruma girdiğimde gördüm .
Üyeliğim bitmiş yandisk benzeri biryere yükleme şansınız var mı?
 
Katılım
6 Temmuz 2008
Mesajlar
1,875
Excel Vers. ve Dili
OFFİCE 2010- TÜRKÇE
OVER ifadesi sql server veritabanı sistemlerine (ms, oracle.. vs) has bir özelliktir. Bilgisayarımda kurulu olan MS Sql server sistemine ilgili Access dosyasını linked server olarak tanıtarak aşağıdaki listeyi kolayca alabildim. Oracle'da nasıl kullanılır hiç bilmiyorum ama, ilgilenirseniz MS Sql Server için Linked Server konusu ile OVER kullanarak çözüme ulaşabiliyorsunuz.

SQL:
SELECT carkod,tarıh, ıslemtıpı,acıklama, ba, tutar,
sum(case ba when 'B' then tutar else -tutar end) over(partition by carkod order by tarıh, KAYITNO) as Bakiye
FROM OPENQUERY(myserver2, 'select * from [CARTH001]')
order by carkod, tarıh
Ekli dosyayı görüntüle 215588


.
Oracle sorguları neredeyse sql ile aynı şirketin tüm veritabanı oracleda
raporlamalarda oracadan direk çekebiliyorum.
Ama kendi çalışmalarımda hep accesi veri tabanı olarak kullandım.
Tabi bu da önüme bir sürü engel çıkardı. Sql kurmaya niyetlendim ama başaramadım doğrusu access daha kolay geldi :
 
Üst