Re: [Programmer-VB] Tanya query lagi ya...

Tue, 04 Jul 2006 00:54:24 -0700

Maaf ya Mas saya mau tanya lagi, saya lagi sedang masukkan user-usernya di query, tapi terlalu panjang dan saya ingin melanjutkan query-nya di baris yang baru namun pakai tanda apa ya supaya bisa nyambung kebawah dan tidak merah tulisannya di query tersebut? contoh:
 
        Adodc1.RecordSource = "SELECT Query1.Kode, Query1.Rinician, SUM(Query1.AE) as AE, SUM(Query1.AN) as AN, SUM(Query1.AW) as AW, SUM(Query1.DC) as DC, SUM(Query1.DD) as DD , SUM(Query1.DL) as DL, SUM(Query1.DN) as DN, SUM(Query1.EF) as EF, SUM(Query1.ES) as ES, SUM(Query1.FAS) as FAS, SUM(Query1.FS) as FS, SUM(Query1.HT) as HT, SUM(Query1.IIS) as IIS, SUM(Query1.IR) as IR, SUM(Query1.KA) as KA, SUM(Query1.KI) as KI, SUM(Query1.KS) as KS, SUM(Query1.MI) as MI, SUM(Query1.MJ) as MJ, SUM(Query1.MS) as MS, SUM(Query1.NN) as NN & _
        FROM (SELECT Kode, Rinician, IIF(User='AE',Jam,0)AS AE, IIF(User='AN',Jam,0)AS AN, IIF(User='AW',Jam,0)AS AW, IIF(User='DC',Jam,0)AS DC, IIF(User='DD',Jam,0) AS DD, IIF(User='DL',Jam,0) as DL, IIF(User='DN',Jam,0) as DN, IIF(User='EF',Jam,0) as EF, IIF(User='ES',Jam,0) as ES, IIF(User='FAS',Jam,0) as FAS, IIF(User='FS',Jam,0) as FS, IIF(User='HT',Jam,0) as HT, IIF(User='IIS',Jam,0) as IIS, IIF(User='IR',Jam,0) as IR, IIF(User='KA',jam,0) as KA, IIF(User='KI',Jam,0) as KI, IIF(User='KS',Jam,0) as KS, IIF(User='MI',Jam,0) as MI, IIF(User='MJ',Jam,0) as MJ, IIF(User='MS',Jam,0) as MS, IIF(User='NN',Jam,0) as NN FROM detail)Query1 GROUP BY Query1.Kode, Query1.Rinician"
 
Apa saya yang salah kasih tanda sambung ya & _ ?
Tks.
 
Salam,
de


dayat <[EMAIL PROTECTED]> wrote:
SELAMAT ya. Klo gitu ubah nama Fieldnya supaya enak dibaca. Seperti:
SELECT Query1.Kode, Query1.Rincian, SUM(Query1. BY)AS BY,
SUM(Query1.DC)AS DC, SUM( Query1.NP)AS NP ...

Klo tidak salah untuk query ini di Jet engine lebih mudah pake perintah:
TRANSFORM ...
SELECT ...
GROUP BY ...
PIVOT ...

karena kita ga perlu memasukkan user satu2, seandainya ada 100 user (bertambah 100 field), alahmak repotnya, hehehe

Salam
--
dayat

On 7/4/06, De ce <[EMAIL PROTECTED]> wrote:
Makasih Mas Dayat, sudah muncul recordnya tapi BY, DC & NP berubah menjadi Expr1002, Expr1003 & Expr1004 ya?
 
Salam,
de

dayat <[EMAIL PROTECTED]> wrote:
dari query ini field BY, DC, NP muncul ga? Tipe data field Jam integer kan? aku pake postgresql dan bisa jalanin query itu, Jet mungkin lain ya. Klo begitu coba query dibawah ini aja, klo sukses or gagal bilang ya.

SELECT Query1.Kode, Query1.Rincian, SUM( Query1. BY), SUM(Query1.DC), SUM( Query1.NP) FROM
(SELECT Kode, Rincian, IIF(User='BY',Jam,0)AS BY, IIF(User='DC',Jam,0)AS DC, IIF(User='NP',Jam,0)AS NP
FROM Table1)Query1
GROUP BY Query1.Kode, Query1. Rincian;

On 7/4/06, De ce <[EMAIL PROTECTED] > wrote:
Pagi Mas, saya sudah coba query-nya tapi yang tampil hanya record dari Kode dan Rincian saja, 
 
Kode         Rincian                     User
0100025    Administration          
0100026    Mentoring                 
0100030    Marketing                  
 
padahal yang diharapkan adalah:
 
Kode         Rincian                     BY          DC           NP
0100025    Administration           16           13            2
0100026    Mentoring                  3             2             0
0100030    Marketing                  0             0             15

Tks.
 
de

dayat < [EMAIL PROTECTED]> wrote:
sori lupa sama field rincian pada klausa GROUP BY

SELECT Kode, Rincian, SUM(BY), SUM(DC), SUM(NP) FROM
(SELECT Kode, Rincian, IIF(User='BY',Jam,0)AS BY, IIF(User='DC',Jam,0)AS DC, IIF(User='NP',Jam,0)AS NP
FROM Table1)Query1
GROUP BY Kode, Rincian;

klo banyak user dan ga mau memasukkan satu2, pake crosstab query. Coba cari di helpnya microsoft access. pake wizard atau baca sql-nya, bila udah jalan dan menghasilkan yang diinginkan copy aja sql-nya ke vb.

Yahoo! Music Unlimited - Access over 1 million songs. Try it free.



--
  Care International Indonesia
        Health Department
Database Management Officer
                    --
          Hidayat (dayat)

Yahoo! Music Unlimited - Access over 1 million songs. Try it free. __._,_.___

Wahana Programmer Groups Links

<*> Untuk mengunjungi sponsor milis ini, klik link berikut:
    http://wahanaprogrammer.net

<*> Untuk menghubungi owner milis ini, kirim email ke:
    [EMAIL PROTECTED]

<*> Konsultasi pemrogramman bisa chat disini:
    Yahoo! Messenger: wahanaprogrammer



YAHOO! GROUPS LINKS



__,_._,___

Kirim email ke