sekedar share pengalaman. sy prnah bikin aplikasi kecil untuk inventory. sy ngitung stock akhir make query, di mana :
stok akir : stok awal + qty masuk + qty keluar pas testing lancar. sebulan lancar. 3 bulan njeblug. load stock sampek 30 - 60 detik. solusinya akhirnya data stock disimpan di tabel master. :D. beda topik ya? issuenya adl make query. ati2. jangan sampek sampean ngalami kayak saya salam, huda --- In [email protected], Darmawan <[EMAIL PROTECTED]> wrote: > kalo maksudnya buat tabel spt ini: > cth: > Mat_Id Tot_Rec tot_Issue > 001 10 5 > 002 20 15 > > mungkin cara ini bukan yg terbaik tp bisa membantu: > 1. buat query crosstab dari tabel RECEIVING (qRec) yang memuat total Qty per Mat_Id. > cth crosstab per tahun: > TRANSFORM Sum(RECEIVING.Qty_Rec) > SELECT RECEIVING.Mat_Id, Sum(RECEIVING.Qty_Rec) AS Tot_Qty_Rec > FROM RECEIVING > GROUP BY RECEIVING.Mat_Id > PIVOT Format(Rec_Date,"yyyy"); > > hasilnya spt: > Mat_Id Tot_Qty_Rec 2005 > 001 10 10 > 002 20 20 > > 2. buat query crosstab dari tabel ISSUE (qIssue) yang memuat total Qty per Mat_Id. > cth crosstab per tahun: > TRANSFORM Sum(ISSUE.Qty_Issue) > SELECT ISSUE.Mat_Id, Sum(ISSUE.Qty_Issue) AS Tot_Qty_Issue > FROM ISSUE > GROUP BY ISSUE.Mat_Id > PIVOT Format(Issue_Date,"yyyy"); > > > hasilnya spt: > Mat_Id Tot_Qty_Issue 2005 > 001 5 5 > 002 15 15 > > 3. terakhir buat query gabungan (qIN_OUT) dari query qRec dan qIssue yang memuat total Qty_Rec dan > Qty_Issue per Mat_Id. > cth crosstab per tahun: > SELECT MASTER.Mat_Id, qRec.Tot_Qty_Rec, qIssue.Tot_Qty_Issue > FROM MASTER LEFT JOIN qRec ON MASTER.Mat_Id = qRec.Mat_Id > LEFT JOIN qIssue ON MASTER.Mat_Id = qIssue.Mat_Id; > > hasilnya spt: Mat_Id Tot_Rec tot_Issue > 001 10 5 > 002 20 15 > > dengan cara ini maka setiap tabel RECEIVING dan ISSUE terupdate, maka query qIN_OUT akan terupdate secara otomatis. > > smoga m'bantu ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12hls7jnn/M=362335.6886445.7839731.1510227/D=groups/S=1705689267:TM/Y=YAHOO/EXP=1123162955/A=2894361/R=0/SIG=13jmebhbo/*http://www.networkforgood.org/topics/education/digitaldivide/?source=YAHOO&cmpgn=GRP&RTP=http://groups.yahoo.com/";>In low income neighborhoods, 84% do not own computers. At Network for Good, help bridge the Digital Divide!</a>.</font> --------------------------------------------------------------------~-> Untuk berhenti berlangganan kirim email kosong ke : [EMAIL PROTECTED] Ikuti juga forum diskusi VB.net dengan mengirim email kosong ke [EMAIL PROTECTED] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indoprog-vb/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
