MS ACCESS MAILING LIST: Re: [belajar-access] mau tanya update query [1 Attachment]

[Attachment(s) from Sumiyanto - included below]

kira-kira hasil sql nya seperti ini:

1. chek dulu data yang sama --> update
UPDATE table2 INNER JOIN table1 ON table2.sample_ID=table1.sample_ID SET table1.au1 = table2.au1, table1.au2 = table2.au2, table1.au3 = table2.au3, table1.au4 = table2.au4, table1.ag1 = table2.ag1, table1.ah1 = table2.ah1, table1.auave = table2.auave, table1.agave = table2.agave;

2. chek data yang tdk ada --> append
INSERT INTO table1 ( sample_ID, au1, au2, au3, au4, ag1, ah1, auave, agave )
SELECT table2.sample_ID, table2.au1, table2.au2, table2.au3, table2.au4, table2.ag1, table2.ah1, table2.auave, table2.agave
FROM table1 RIGHT JOIN table2 ON table1.sample_ID=table2.sample_ID
WHERE (((table1.sample_ID) Is Null));

Regards,

Sumiyanto

Terima kasih: him mah <himmah.mlg@gmail.com> sudah mereply posting "suhu MBA"

From: him mah <himmah.mlg@gmail.com>
To: belajar-access@yahoogroups.com
Cc:
Sent: Wednesday, November 3, 2010 3:13:15 PM
Subject: Re: [belajar-access] mau tanya update query

mungkin ini bisa membantu, dari arsip milis sebelumnya yang di posting
oleh pak MBA

ada dua langkah pak Johan,

1. chek dulu data yang sama --> update
2. chek data yang tdk ada --> append

contoh.

tContoh
ID Test
1 one
2 two
3 three
4 four

tDataInput
ID Test
1 satu
5 five

query1:
UPDATE tDataInput INNER JOIN tContoh ON tDataInput.ID = tContoh.ID SET
tContoh.Test = [tDataInput].[Test];
query2:
INSERT INTO tContoh ( ID, Test )
SELECT tDataInput.ID, tDataInput.Test
FROM tContoh RIGHT JOIN tDataInput ON tContoh.ID = tDataInput.ID
WHERE (((tContoh.ID) Is Null));
kalo mau gampang dua query ini di masukkan macro.
data di taruh di tDataInput. panggil macro. dua query dieksekusi.

hasilnya:
tContoh
ID Test
1 satu
2 two
3 three
4 four
5 five

salam,
murid access - CL

2010/11/3, Rusmanto <rusmanto@tokatindung.com>:
> Betul sekali Mas Deni, kadang semau field di update tapi kadang juda tidak,
> jadi intinya gimana caranya
> supaya update ke Tabel 1 dari Tabel 2 itu bisa mengganti nilai2 di field
> Tabel 1 baik semua atau hanya sebagaian.
>
> makasih Mas Deni kalau ada caranya kasih tahu neh...... biar saya juga ikut
> belajar.
>
> salam
> Rusman
>
>
> _____
>
> From: Deni Agus [mailto:dr.deniagus@yahoo.co.id]
> To: belajar-access@yahoogroups.com
> Sent: Wed, 03 Nov 2010 06:28:42 +0000
> Subject: Re: [belajar-access] mau tanya update query
>
>
>
>
>
>
> ikutan bertanya ya Mas, kalo gak sama pertanyaannya ya ndak papa!! (biar
> sekaligus dijawab amam pakar"nya access)
>
> Mas rusman, apa 10 record yang mau dimasukkan tadi akan mengganti semua
> field yang ada. (record di update)
>
> misal 1011260 yang mau diganti :
>
> sample_id au1 au2 au3 au4 ag1 hg1 auave agave
> 1011260 3.25 3.32 1.2 0.25 3.285 1.2
>
> menjadi :
>
> sample_id au1 au2 au3 au4 ag1 hg1 auave agave
> 1011260 1,2 1,3 1.4 0,1 2.2 0.2
>
> ato hanya field tertentu.
> maap kalo ikutan nanya, coz masalahnya sama ama yang aku adepin juga.
>
> Trims,
>
>
>
>
>
> *** This e-mail and attachments it might contains has passed internal
> anti-virus check but it is suggested for you to re-scan with your anti-virus
> ***
>
> Disclaimer Notice :
> This message and any attachment contains information intended only for the
> use of the addresee named above. It may also be confidential and/or
> privileged.
> If you are not the intended recipient of this message you are herby notified
> that you must not disseminate, copy or take any action in reliance on it.
> Please delete the message and notify the sender.
> Opinions, conclusions and other information in this message that do not
> relate to the official business of Archipelago Resources Plc., Archipelago
> Resources Pty Ltd.,
> PT Meares Soputan Mining, PT Tambang Tondano Nusajaya shall be understood as
> neither given nor endorsed by them.
>

Attachment(s) from Sumiyanto -

1 of 1 File(s)