MS ACCESS MAILING LIST: Re: [belajar-access] running sum [1 Attachment]

[Attachment(s) from Sumiyanto - included below]

Terima kasih Mas Fajar dan Ok bang Aksan sampe di sini tidak ada problem, Adapun Output yang diinginkan adalah, setiap pembelian ini adalah di anggap Deposit ke Supplier "Bimoli, Filma atau lainnya" kemudian transaksi penjualan adalah mengurangi Deposit tersebut. Customer di bagi 2, yakni Agen dan Customer retail, jika Agen mengurangi Saldo Deposit Agen ke Distributor juga mengurangi saldo Deposit Distributor yang ada di Supplier, tetapi jika Customer biasa mengurangi Deposit Distributor di supplier.

Hirarkinya sbb:
Supplier
---> Distributor (tidak saya definisikan, karena pada dasarnya sama dengan Customer)
--->--->Customer (C001 dst)
--->--->Agen (A001 dst)

tblPenjualanHeader(ID, trx, tgl,CustID)
tblPenjualanDetail(ID, trx,CodeBrg, qty, price)
tblPembelianHeader(ID, trx, tgl,SuppID)
tblPembelianDetail (ID, trx, CodeBrg, qty, price)
M_Brg(CodeBrg, NamaBrg, Price)
M_Cust(CustID, NamaCust)
M_Supp(SuppID, NamaSupp)

Mohon petunjuk lagi... tentang running sum balance dari deposit/pembelian - penjualan per masing-supplier/Agen

Salam,
Sumiyanto

--- On Thu, 4/7/11, Aksan Kurdin <aksan.kurdin@gmail.com> wrote:

From: Aksan Kurdin <aksan.kurdin@gmail.com>
Subject: Re: [belajar-access] running sum
To: belajar-access@yahoogroups.com
Date: Thursday, April 7, 2011, 10:30 AM

Mainin di query cak sum:
SELECT 'J' AS TrxType, JH.ID, JH.trx, JH.tgl, JD.qty, JD.price, -(JD.qty * JD.price) AS Amount FROM tblpenjualanheader AS JH INNER JOIN tblPenjualanDetail as JD ON JH.trx = JD.trx
UNION ALL
SELECT 'B' AS TrxType, BH.ID, BH.trx, BH.tgl, BD.qty, BD.price, BD.qty * BD.price AS Amount FROM tblpembelianheader as BH INNER JOIN tblpembeliandetail as BD ON BH.trx = BD.trx

Sekarang tinggal group by TrxType di report, dan jalankan running sum over group.

aksan kurdin

On 4/7/2011 9:27 AM, Sumiyanto - wrote:

mengenai running sum, ada beberapa hal yang ingin saya tanyakan:
tblPenjualanHeader(ID, trx, tgl)
tblPenjualanDetail(ID, trx, qty, price)
tblPembelianHeader(ID, trx, tgl)
tblPembelianDetail (ID, trx, qty, price)

jika di buat query union antara transaksi penjualan dan pembelian otomatis terdapat record ID yang mungkin sama, sehingga dalam kasus running sum berdasarkan ID tidak bisa terpenuhi. Mohon saran tentang running sum...

berikut link tentang running sum:
http://support.microsoft.com/search/default.aspx?mode=r&query=running+sum+ms+access&spid=global&catalog=LCID%3D1033&1033comm=1&ast=25&ast=28&ast=30&ast=31&res=10
http://office.microsoft.com/en-us/access-help/calculate-a-running-sum-cumulative-total-HP005187388.aspx
http://support.microsoft.com/kb/205183
http://office.microsoft.com/en-us/access-help/summing-in-reports-HA001122444.aspx
http://office.microsoft.com/en-us/access-help/create-an-expression-HA010341590.aspx?CTT=5&origin=HA010341571
http://support.microsoft.com/kb/208714
http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx
http://support.microsoft.com/kb/210338
http://support.microsoft.com/kb/296249

Salam,
Sumiyanto