----- 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