Bisa dieksekusi dengan 1 query. Namun sebelumnya harus membuat module dulu:
Option Explicit
Function saldo_p(ByVal RecordID As Variant, nmtabel As Variant _
, fi_db As Variant, fi_kr As Variant, n_id As Variant) As Double
Dim hs As Double
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select sum(" & fi_db & ") From " _
& nmtabel & " where " & n_id & "<=" & RecordID)
If Not rs.EOF Then
hs = Nz(rs.Fields(0), 0)
Else
hs = 0
End If
rs.Close
Set rs = Nothing
Set rs = CurrentDb.OpenRecordset("Select sum(" & fi_kr & ") From " _
& nmtabel & " where " & n_id & "<=" & RecordID)
If Not rs.EOF Then
hs = hs - Nz(rs.Fields(0), 0)
Else
hs = hs - 0
End If
rs.Close
Set rs = Nothing
saldo_p = hs
End Function
Perhatikan variabel yang harus diumpankan pada fungsi saldo_p:
ByVal RecordID As Variant, nmtabel As Variant _
, fi_db As Variant, fi_kr As Variant, n_id As Variant
RecordID = id unik dari record (number)
nmtabel = nama tabel
fi_db = nama field debet (number)
fi_kr = nama field kredit (number)
n_id = nama field id (number)
Bila itu kita eksekusi, logikanya akan berjalan seperti ini:
- Hitung jumlah field debet dimana idnya <= yang diumpankan.
- Hitung jumlah field kredit dimana idnya <= yang diumpankan.
- Sampaikan hasilnya ke user.
Dalam kasus Sampeyan, tabelnya (misal bernama t) harus ditambah 1 field id unik (type data number; harus berisi bila ada record). Jika nama tersebut adalah id, buatlah query berikut:
+-------+-------+-------+--------+--------------------------------------+
| Date | COA | Debit | Credit | Saldo: saldo_p([id];"t";"Debit";"Credit ";"id") |
+-------+-------+-------+--------+--------------------------------------+
Maka akan terbentuk nilai saldonya.
Semoga bisa membantu dan memberi semangat.
Hariyanto (Surabaya)
--------------------------------------------
On Tue, 25/2/14, kbasuseno@yahoo.com <kbasuseno@yahoo.com> wrote:
Subject: [belajar-access] Simpan 1set query untuk dipakai di query berikutnya
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Date: Tuesday, 25 February, 2014, 6:21 AM
Dear Pakar,
Mau tanya
Misalkan kita ada data sbb :
Date COA Debit Credit
01-Jan 11101 100 -
01-Jan 11101 200 -
01-Jan 11101 150 -
01-Jan 11101 - 50
02-Jan 11101 - 50
02-Jan 11101 200
02-Jan 11101 100 -
02-Jan 11101 200 -
02-Jan 11101 150 -
02-Jan 11101 - 50
03-Jan 11101 - 50
03-Jan 11101 200
03-Jan 11101 100 -
03-Jan 11101 200 -
03-Jan 11101 150 -
03-Jan 11101 - 50
Saya ingin pecah data tersebut menjadi Opening
Balance, Transaction dan Ending Balance
Opening Balance :
Select COA, sum(Debit) As OBDebit, Sum(Credit)
As OBCredit From dbo.tableName
Where Doc_date '2014-02-01')
Transaction :
Select COA, sum(Debit) As TransDebit,
Sum(Credit) As TransCredit From dbo.tableName
Where Doc_date Between '2014-02-01' and
'2014-03-01'
Ending Balance :
EBDebit = OBDebit + TransDebit - TransCredit
EBCredit = OBCredit - TransDebit + TransCredit
Apakah bisa dibuat dalam 1 query / sp di sql
server?
selama ini saya buat dulu 2 view baru di joint di sp, apakah
bisa langsung dibuat dalam setu sp saja?
terima kasih
Sent
from Yahoo Mail on Android
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar