[belajar-access] Perhitungan Stock

----- Original Message -----
From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Thursday, June 18, 2009 1:46 AM
Subject: Re: [belajar-access] Perhitungan Stock

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: belajar-access@yahoogroups.com
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 <yufman@yahoo.com>
To: belajar-access@yahoogroups.com
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.