Ada ide sbb, asumsinya nama tabelnya adalah datacontoh: 1. Buat query union, misalnya: query10 2. query union tsb di crosstab, misalnya: query10_Crosstab
query10: SELECT 1 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<1)); UNION SELECT 1 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=1)); UNION SELECT 1 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=1)); UNION SELECT 1 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=1)); UNION SELECT 2 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<2)); UNION SELECT 2 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=2)); UNION SELECT 2 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=2)); UNION SELECT 2 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=2)); UNION SELECT 3 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<3)); UNION SELECT 3 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=3)); UNION SELECT 3 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=3)); UNION SELECT 3 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=3)); UNION SELECT 4 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<4)); UNION SELECT 4 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=4)); UNION SELECT 4 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=4)); UNION SELECT 4 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=4)); UNION SELECT 5 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<5)); UNION SELECT 5 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=5)); UNION SELECT 5 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=5)); UNION SELECT 5 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=5)); UNION SELECT 6 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<6)); UNION SELECT 6 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=6)); UNION SELECT 6 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=6)); UNION SELECT 6 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=6)); UNION SELECT 7 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<7)); UNION SELECT 7 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=7)); UNION SELECT 7 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=7)); UNION SELECT 7 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=7)); UNION SELECT 8 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<8)); UNION SELECT 8 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=8)); UNION SELECT 8 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=8)); UNION SELECT 8 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=8)); UNION SELECT 9 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<9)); UNION SELECT 9 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=9)); UNION SELECT 9 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=9)); UNION SELECT 9 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=9)); UNION SELECT 10 AS [No], '1-StockAwal' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<10)); UNION SELECT 10 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=10)); UNION SELECT 10 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=10)); UNION SELECT 10 AS [No], '4-Stock' as Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=10)); query10_Crosstab: TRANSFORM Sum(Query10.Jumlah) AS Jumlah SELECT Query10.[No] FROM Query10 GROUP BY Query10.[No] PIVOT Query10.Tag; salam kompak, hangat dan jabat erat, MBA ----- Original Message ----- From: luqman permata To: [email protected] Sent: Monday, June 15, 2009 12:48 Subject: Re: [belajar-access] Perhitungan Stock makasih sebelumnya sebenarnya kemarin sudah kepikir pakai union. tapi saya belum ketemu bagaimana logikanya. mas yuf kalau ada ide bisa share lah. Luqman ------------------------------------------------------------------------------ From: yuf yufman <[email protected]> To: [email protected] Sent: Monday, June 15, 2009 12:11:08 PM Subject: Re: [belajar-access] Perhitungan Stock pak bagaimana klo menggunakan Query Union.... --- On Sun, 6/14/09, Murid Belajar Access <murid.belajaraccess @yahoo.co. id> wrote: From: Murid Belajar Access <murid.belajaraccess @yahoo.co. id> Subject: Re: [belajar-access] Perhitungan Stock To: belajar-access@ yahoogroups. com Date: Sunday, June 14, 2009, 6:24 PM Pak Luqman, Sekedar ide .. Misalnya data ditaruh di tabel DataContoh. SELECT Datacontoh.[ No], Datacontoh.Masuk, Datacontoh.Keluar, -nz(DSum("keluar" ,"datacontoh" ,"[no]<=" & [No]),0)+nz( DSum("masuk" ,"datacontoh" ,"[no]<=" & [No]),0) AS Total FROM Datacontoh; tapi, karena ini pake dsum, biasanya akan lambat untuk pemakaian data yang banyak. mungkin kalau sdh dirasa lambat bisa dibuatkan fungsi buatan sendiri / prosedur untuk menghitung stock tsb. Salam Hangat dan Jabat Erat, Murid Belajar Access ----- Original Message ----- From: luqman permata To: belajar-access@ yahoogroups. com Sent: Saturday, June 13, 2009 08:30 Subject: Re: [belajar-access] Akhirnya Ketemu Juga assalamu'alaikum Alhamdulillah akhirnya ketemu juga dengan milis ini. o ya saya mau tanya adakah yang tahu bagaimana caranya saya membuat query yang bisa menghitung stock seperti dibawah. saya sudah buntu alias mentok. No Masuk Keluar Stock 1 10 10 2 1 9 3 1 8 4 1 7 5 1 6 6 1 5 7 2 7 8 1 6 9 1 5 10 1 4 terima kasih sebelumnya Luqman.
