Ibu, field nkkrs sebaiknya jangan di set sebagai AutoNumber, diset number
(integer) saja, lalu jadikan sebagai primary key, sehingga primary key nya jadi
ada 3. Setelah itu ibu bisa buat module baru, copy paste & run procedure (F5)
sbb:
Function test()
Dim a As Integer, b As Integer, c As Integer, d As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset, rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Kekerasan", dbOpenDynaset)
rs.MoveFirst
DoCmd.SetWarnings False
While Not rs.EOF
a = IIf(IsNull(rs!Eks), 0, 1)
b = IIf(IsNull(rs!Clk), 0, 1)
c = IIf(IsNull(rs!KKRS), 0, 1)
d = IIf(IsNull(rs!KOS), 0, 1)
If a = 1 Then DoCmd.RunSQL "INSERT INTO TVio ( nk, nko, nkkrs,
penjelasan, jenis ) SELECT Kekerasan.nk, Kekerasan.nko, " & a & " AS nkkrs,
Kekerasan.nama, 'EKS' AS jenis FROM Kekerasan WHERE (((Kekerasan.nk)='" &
rs![nk] & "') AND ((Kekerasan.nko)='" & rs![nko] & "'));"
If b = 1 Then DoCmd.RunSQL "INSERT INTO TVio ( nk, nko, nkkrs,
penjelasan, jenis ) SELECT Kekerasan.nk, Kekerasan.nko, " & a + b & " AS nkkrs,
Kekerasan.nama, 'CLK' AS jenis FROM Kekerasan WHERE (((Kekerasan.nk)='" &
rs![nk] & "') AND ((Kekerasan.nko)='" & rs![nko] & "'));"
If c = 1 Then DoCmd.RunSQL "INSERT INTO TVio ( nk, nko, nkkrs,
penjelasan, jenis ) SELECT Kekerasan.nk, Kekerasan.nko, " & a + b + c & " AS
nkkrs, Kekerasan.nama, 'KKRS' AS jenis FROM Kekerasan WHERE (((Kekerasan.nk)='"
& rs![nk] & "') AND ((Kekerasan.nko)='" & rs![nko] & "'));"
If d = 1 Then DoCmd.RunSQL "INSERT INTO TVio ( nk, nko, nkkrs,
penjelasan, jenis ) SELECT Kekerasan.nk, Kekerasan.nko, " & a + b + c + d & "
AS nkkrs, Kekerasan.nama, 'KOS' AS jenis FROM Kekerasan WHERE
(((Kekerasan.nk)='" & rs![nk] & "') AND ((Kekerasan.nko)='" & rs![nko] & "'));"
rs.MoveNext
Wend
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
MsgBox "Proses Selesai."
End Function
Hasil pada table TVio:
TVio
No Kejadian
No Korban
nkkrs
jenis
penjelasan
1 1 1 EKS Ani
1 1 2 CLK Ani
1 1 3 KKRS Ani
1 1 4 KOS Ani
1 2 1 CLK Susi
1 2 2 KKRS Susi
1 2 3 KOS Susi
1 3 1 EKS Ali
1 3 2 CLK Ali
1 3 3 KKRS Ali
Thank's.
________________________________
From: erna yanti <[email protected]>
To: belajar access <[email protected]>
Sent: Tue, January 26, 2010 4:54:54 PM
Subject: [belajar-access] (unknown)
Maaf teman-teman semua
Saya lg merenovasi database lama, pada database baru, ada tabel baru (TVio) yg
hrs ditambahkan tp datanya ada di tabel TKorban.
tabel lama, TKorban, sudah di definisikan sesuai dengan yg dibutuhkan sekarang.
No Kejadian
No Korban
nama
Eks
Clk
KKRS
KOS
01 01 Ani EKS CLK KKRS KOS
01 02 Susi
CLK KKRS KOS
01 03 Ali EKS CLK KKRS
Tapi, yg dibutuhkan kira2 seperti tabel ini (Tvio).
No Kejadian No Korban No Kekerasan Nama korban Jenis Kekerasan
01 01 01 Ani EKS
01 01 02 Ani CLK
01 01 03 Ani KKRS
01 01 04 Ani KOS
01 02 01 Susi CLK
01 02 02 Susi KKRS
01 02 03 Susi KOS
01 03 01 Ali EKS
01 03 02 Ali CLK
01 03 03 Ali KKRS
mungkin diantara teman2 ada yg pernah buat yg rada2 mirip,
salam,
erna
