iya, bung arsen sudah lama sekali ya ....
coba begini deh, dari satu tabel barang.dbf, link ke access dan
'modifikasi' strukturnya menjadi suatu query seperti ini:
SELECT 0 as bulan, KodeBrg, 0 as [In], 0 as RpIn, 0 as [Out], 0 as RpOut,
0 as AdjInIn, 0 as RpAdjIn, 0 as AdjOut, 0 as RpAdjOut,
QtyAwal as Sisa, 0 as RpSisa
FROM barang
UNION ALL
SELECT 1 as bulan, KodeBrg, In1 as [In], RpIn1 as RpIn, Out1 as [Out],
RpOut1 as RpOut,
AdjInIn1 as AdjInIn, RpAdjIn1 as RpAdjIn, AdjOut1 as AdjOut,
RpAdjOut1 as RpAdjOut,
Sisa1 as Sisa, RpSisa1 as RpSisa
FROM barang
UNION ALL
SELECT 2 as bulan, KodeBrg, In2 as [In], RpIn2 as RpIn, Out2 as [Out],
RpOut2 as RpOut,
AdjInIn2 as AdjInIn, RpAdjIn2 as RpAdjIn, AdjOut2 as AdjOut,
RpAdjOut2 as RpAdjOut,
Sisa2 as Sisa, RpSisa2 as RpSisa
FROM barang
....
UNION ALL
SELECT 12 as bulan, KodeBrg, In12 as [In], RpIn12 as RpIn, Out12 as
[Out], RpOut12 as RpOut,
AdjInIn12 as AdjInIn, RpAdjIn12 as RpAdjIn, AdjOut12 as AdjOut,
RpAdjOut12 as RpAdjOut,
Sisa12 as Sisa, RpSisa12 as RpSisa
FROM barang
Nah beri nama query diatas dengan qu_Barang.
Sekarang arsen sudah bisa kerja dengan mudah bukan ?
PARAMETERS nBulan SHORT
SELECT KodeBrg,
(Select Sisa FROM qu_Barang WHERE bulan=[nBulan]-1 AND
KodeBrg=qu_Barang.KodeBrg)
as [Saldo Awal],
(Select RpSisa FROM qu_Barang WHERE bulan=[nBulan]-1 AND
KodeBrg=qu_Barang.KodeBrg)
as [Rp Saldo Awal],
[In], RpIn, [Out], RpOut, AdjIn, RptAdjIn, AdjOut, RptAdjOut, Sisa, RpSisa
FROM qu_Barang
WHERE bulan = [nBulan]
ini belum saya test, mungkin ada kesalahan sintak, terutama di sub
query, tapi coba dulu ya ....., yang penting idenya mengubah struktur
tabel lewat query bisa ditangkap .....
aksan kurdin
> Saldo Awal: Sisa7
>
> Qty In: In8
>
> Qty Out: Out8
>
> Adjustment In: AdjIn8
>
> Adjustment Out: AdjOut8
>
> Saldo Akhir: Sisa8
>
> (Plus dengan Rupiahnya)
On Fri, Jan 2, 2009 at 12:00 PM, Arsenius Sutandio
<[email protected]> wrote:
> Dear Accesser,
>
>
>
> Udah lama nih nggak nongol di milis, hehe..
>
> Saya mau tanya nih.. Saya mau ambil data dari .dbf (programnya pake Clipper)
>
> Tabelnya begini (Panjang banget!):
>
> NamaTabel: Barang.dbf
>
> KodeBrg
>
> QtyAwal
>
> In1
>
> RpIn1
>
> Out1
>
> RpOut1
>
> AdjInIn1
>
> RpAdjIn1
>
> AdjOut1
>
> RpAdjOut1
>
> Sisa1
>
> RpSisa1
>
>
>
> In2
>
> RpIn2
>
> Out2
>
> RpOut2
>
> AdjIn2
>
> RpAdjIn2
>
> AdjOut2
>
> RpAdjOut2
>
> Sisa2
>
> RpSisa2
>
> …
>
> In12
>
> RpIn12
>
> Out12
>
> RpOut12
>
> AdjIn12
>
> RpAdj
>
> AdjOut12
>
> Sisa12
>
> RpSisa12
>
>
>
> Misalkan saya mau ambil data bulan 8, maka tabel yang harus saya ambil
> adalah:
>
> Saldo Awal: Sisa7
>
> Qty In: In8
>
> Qty Out: Out8
>
> Adjustment In: AdjIn8
>
> Adjustment Out: AdjOut8
>
> Saldo Akhir: Sisa8
>
> (Plus dengan Rupiahnya)
>
>
>
> Saya sudah bikin query-nya pakai IIF, tapi panjang dan lama prosesnya,
> bahkan kalau sudah cek Adjustment, Query-nya nolak karena 'too complex to
> evaluate'
>
> Saya akalin pakai: "Barang.In" & [Bulan] – 1, yang keluar malah textnya:
> "Barang.In7"
>
>
>
> Mohon bantuan solusinya. Thx
>
>
>
> Regards,
>
> Arsenius
>
>
>
>
>
>
--
Aksan Kurdin
------------------------------------
SPAM IS PROHIBITEDYahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/belajar-access/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/belajar-access/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[email protected]
mailto:[email protected]
<*> 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/
