Accesden bakıye sorgusu

acar6783 · 22 Mart 2020

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.

ACCESS QUERY.rar

View and download from Yandex.Disk

yadi.sk

acar6783 · 23 Mart 2020

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 · 23 Mart 2020

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.

.

acar6783 · 23 Mart 2020

Zeki Gürsoy' Alıntı:
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 · 23 Mart 2020

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.

.

acar6783 · 23 Mart 2020

@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

acar6783 · 23 Mart 2020

Zeki Gürsoy' Alıntı:
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 · 23 Mart 2020

En azından doğru çalışıyor gibi ....

.

acar6783 · 23 Mart 2020

Haluk' Alıntı:
En azından doğru çalışıyor gibi ....

.

Aynen artık ne kadar geliştirebilirim bilmiyorum ama şu an için en iyisi bu gibi

acar6783 · 23 Mart 2020

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 · 23 Mart 2020

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 · 24 Mart 2020

7.mesajınızdaki koda alternatif, biraz daha hızlı dosya ektedir...

.

Zeki Gürsoy · 24 Mart 2020

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

.

Haluk · 24 Mart 2020

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 · 24 Mart 2020

Elbette, hatta text dosyaları için bile. Ücretsiz versiyon olan Express işimizi görür.

Linked server detaylar : https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver15

Düzenleme,
OVER Clause : https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

.

Haluk · 24 Mart 2020

Teşekkürler Üstad, inceleyecek yeni bir konu çıktı ....

.

Zeki Gürsoy · 24 Mart 2020

İyi o zaman... Visual Studio'dan sonra Sql Server macerasına başlayalım artık.

acar6783 · 24 Mart 2020

Haluk' Alıntı:
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ı?

Haluk · 24 Mart 2020

ADO_YuruyenBakiye.rar

drive.google.com

.

acar6783 · 24 Mart 2020

Zeki Gürsoy' Alıntı:
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 :

Accesden bakıye sorgusu

acar6783

ACCESS QUERY.rar

acar6783

Zeki Gürsoy

Uzman

acar6783

Zeki Gürsoy

Uzman

acar6783

acar6783

Haluk

Özel Üye

acar6783

acar6783

Haluk

Özel Üye

Haluk

Özel Üye

Ekli dosyalar

Zeki Gürsoy

Uzman

Haluk

Özel Üye

Zeki Gürsoy

Uzman

Haluk

Özel Üye

Zeki Gürsoy

Uzman

acar6783

Haluk

Özel Üye

ADO_YuruyenBakiye.rar

acar6783