From: Sofyan Efendi <sofyanefendi@gmail.com>
To: belajar-access@yahoogroups.com
Sent: Tue, May 17, 2011 10:18:38 AM
Subject: Re: [belajar-access] Tanya crosstab query menampilkan seluruh data [1 Attachment]
Dear Mas Fajar, karena cara kerja crosstab harus ada data walaupun null, maka tahap pertama harus membuat dahulu table temporary, misal table "tmpCrosstab" yg didalamnya memuat field:
- hari
- id_ruang_kelas
- nama_ruang
- id_jam
- jam
Fungsinya untuk menampung seluruh data yang ada walaupun kosong. Save table diatas lalu close. Kemudian buat procedure:
Function buat_mata_pelajaran()
Dim db As Database
Dim rs As Recordset, rs1 As Recordset, rs2 As Recordset, rs3 As Recordset
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tmpCrosstab.* FROM tmpCrosstab;"
DoCmd.SetWarnings True
Set db = CurrentDb()
strSQL1 = "SELECT DISTINCT tb_guru_mapel.hari FROM tb_guru_mapel;"
strSQL2 = "SELECT tb_ruang_kelas.id_ruang_kelas, tb_ruang_kelas.nama_ruang FROM tb_ruang_kelas;"
strSQL3 = "SELECT tb_jam.id_jam, CStr(Format([tb_jam].[jam_mulai],'Short Time'))+' - '+CStr(Format([tb_jam].[jam_selesai],'Short Time')) AS jam FROM tb_jam;"
Set rs = db.OpenRecordset("tmpCrosstab", dbOpenDynaset)
Set rs1 = db.OpenRecordset(strSQL1, dbReadOnly)
Set rs2 = db.OpenRecordset(strSQL2, dbReadOnly)
Set rs3 = db.OpenRecordset(strSQL3, dbReadOnly)
Do While Not rs1.EOF
rs2.MoveFirst
Do While Not rs2.EOF
rs3.MoveFirst
Do While Not rs3.EOF
rs.AddNew
rs!hari = rs1!hari
rs!id_ruang_kelas = rs2!id_ruang_kelas
rs!nama_ruang = rs2!nama_ruang
rs!id_jam = rs3!id_jam
rs!jam = rs3!jam
rs.Update
rs3.MoveNext
Loop
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs.Close
rs1.Close
rs2.Close
rs3.Close
db.Close
End Function
Dim db As Database
Dim rs As Recordset, rs1 As Recordset, rs2 As Recordset, rs3 As Recordset
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tmpCrosstab.* FROM tmpCrosstab;"
DoCmd.SetWarnings True
Set db = CurrentDb()
strSQL1 = "SELECT DISTINCT tb_guru_mapel.hari FROM tb_guru_mapel;"
strSQL2 = "SELECT tb_ruang_kelas.id_ruang_kelas, tb_ruang_kelas.nama_ruang FROM tb_ruang_kelas;"
strSQL3 = "SELECT tb_jam.id_jam, CStr(Format([tb_jam].[jam_mulai],'Short Time'))+' - '+CStr(Format([tb_jam].[jam_selesai],'Short Time')) AS jam FROM tb_jam;"
Set rs = db.OpenRecordset("tmpCrosstab", dbOpenDynaset)
Set rs1 = db.OpenRecordset(strSQL1, dbReadOnly)
Set rs2 = db.OpenRecordset(strSQL2, dbReadOnly)
Set rs3 = db.OpenRecordset(strSQL3, dbReadOnly)
Do While Not rs1.EOF
rs2.MoveFirst
Do While Not rs2.EOF
rs3.MoveFirst
Do While Not rs3.EOF
rs.AddNew
rs!hari = rs1!hari
rs!id_ruang_kelas = rs2!id_ruang_kelas
rs!nama_ruang = rs2!nama_ruang
rs!id_jam = rs3!id_jam
rs!jam = rs3!jam
rs.Update
rs3.MoveNext
Loop
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs.Close
rs1.Close
rs2.Close
rs3.Close
db.Close
End Function
Fungsinya untuk menambahkan data2 kelas, hari dan jam yang ada, walaupun datanya kosong. Kemudian jalankan di immediate window procedure diatas:
?buat_mata_pelajaran() --> Tekan Enter
Maka dalam table tmpCrosstab akan didapat data semuanya. Kemudian buat query sbb:
SELECT tmpCrosstab.hari, tmpCrosstab.id_ruang_kelas, tmpCrosstab.nama_ruang, tmpCrosstab.id_jam, tmpCrosstab.jam, [tb_guru].[nama_guru]+'-'+[tb_guru_mapel].[kelas]+'-'+[tb_mapel].[nama_mapel] AS nama_guru
FROM tb_mapel RIGHT JOIN (tb_mapel_tersedia RIGHT JOIN (tb_guru RIGHT JOIN (tmpCrosstab LEFT JOIN tb_guru_mapel ON (tmpCrosstab.hari = tb_guru_mapel.hari) AND (tmpCrosstab.id_ruang_kelas = tb_guru_mapel.id_ruang_kelas) AND (tmpCrosstab.id_jam = tb_guru_mapel.id_jam)) ON tb_guru.id_guru = tb_guru_mapel.id_guru) ON tb_mapel_tersedia.id_mapel_tersedia = tb_guru_mapel.id_mapel_tersedia) ON tb_mapel.id_mapel = tb_mapel_tersedia.id_mapel;
FROM tb_mapel RIGHT JOIN (tb_mapel_tersedia RIGHT JOIN (tb_guru RIGHT JOIN (tmpCrosstab LEFT JOIN tb_guru_mapel ON (tmpCrosstab.hari = tb_guru_mapel.hari) AND (tmpCrosstab.id_ruang_kelas = tb_guru_mapel.id_ruang_kelas) AND (tmpCrosstab.id_jam = tb_guru_mapel.id_jam)) ON tb_guru.id_guru = tb_guru_mapel.id_guru) ON tb_mapel_tersedia.id_mapel_tersedia = tb_guru_mapel.id_mapel_tersedia) ON tb_mapel.id_mapel = tb_mapel_tersedia.id_mapel;
Save dengan nama "QueryUntukCrosstab", lalu buatlah query crosstab, misal:
TRANSFORM First(QueryUntukCrosstab.nama_guru) AS FirstOfnama_guru
SELECT QueryUntukCrosstab.id_jam, QueryUntukCrosstab.jam
FROM QueryUntukCrosstab
WHERE (((QueryUntukCrosstab.hari)="senin"))
GROUP BY QueryUntukCrosstab.id_jam, QueryUntukCrosstab.jam
PIVOT QueryUntukCrosstab.nama_ruang;
SELECT QueryUntukCrosstab.id_jam, QueryUntukCrosstab.jam
FROM QueryUntukCrosstab
WHERE (((QueryUntukCrosstab.hari)="senin"))
GROUP BY QueryUntukCrosstab.id_jam, QueryUntukCrosstab.jam
PIVOT QueryUntukCrosstab.nama_ruang;
Maka hasilnya :
| |||||||
Crosstab_Harapan | |||||||
id_jam | jam | A | B | C | D | E | F |
---|---|---|---|---|---|---|---|
2 | 08:00 - 09:30 | Ardi-Kelas 2-B. Indonesia | |||||
3 | 09:30 - 11:00 | Hendra-Kelas 3-B. Indonesia | |||||
4 | 11:00 - 12:30 | Iis-Kelas 4-English | |||||
5 | 13:00 - 14:30 | ||||||
6 | 15:00 - 16:30 | ||||||
7 | 16:30 - 18:00 |
Untuk mempermudah, silahkan download sample di attachment file.
Pada 17 Mei 2011 08:19, FAJAR N <safety_zone1@yahoo.com> menulis:
[Attachment(s) from FAJAR N included below]
Saya ingin membuat crosstab query yg menampilkan jadwal pelajaran disebuah LBB tiap ruang kelas pada tiap jam nya.Jumlah kelas dinamis berdasarkan tabel ruang kelas. Nah, bagaimana tetap menampilkan ruang kelas yg pada hari ituternyata tidak ada jadwal pembelajaran? Jd byr tahu bahwa ruang kelasnya kosong. Kalau default crosstab query kanhanya menampilkan record yg terelasi atau dlm irisan saja.TRANSFORM Nz(First(tb_guru.nama_guru+'-'+tb_guru_mapel.kelas+'-'+tb_mapel.nama_mapel),"-") AS FirstOfnama_guruSELECT tb_jam.id_jam, CStr(Format(tb_jam.jam_mulai,'Short Time'))+' - '+CStr(Format(tb_jam.jam_selesai,'Short Time')) AS Expr1FROM tb_ruang_kelas RIGHT JOIN (tb_mapel INNER JOIN (tb_mapel_tersedia INNER JOIN (tb_jam INNER JOIN (tb_guru INNER JOIN tb_guru_mapel ON tb_guru.id_guru = tb_guru_mapel.id_guru) ON tb_jam.id_jam = tb_guru_mapel.id_jam) ON tb_mapel_tersedia.id_mapel_tersedia = tb_guru_mapel.id_mapel_tersedia) ON tb_mapel.id_mapel = tb_mapel_tersedia.id_mapel) ON tb_ruang_kelas.id_ruang_kelas = tb_guru_mapel.id_ruang_kelasGROUP BY tb_jam.id_jam, CStr(Format(tb_jam.jam_mulai,'Short Time'))+' - '+CStr(Format(tb_jam.jam_selesai,'Short Time'))PIVOT tb_ruang_kelas.nama_ruang;
__._,_.___
SPAM IS PROHIBITED
.
__,_._,___
Tidak ada komentar:
Posting Komentar