Dear Bpk Rusmanto,
Maaf baru sempat membaut picture nya, walaupun sudah ada alternatif dari jawaban pak sudarsono dgn query, module ini hanya pembelajaran alternatif lain.
1. Buat module : modAverage5Field (copy paste dari thread sebelum ini)
2. Buat tableAVG
| TableAVG | |||||
| samno | data1 | data2 | data3 | data4 | data5 |
| 1005151 | 0.18 | 2.1 | 2 | 1.6 | |
| 1005201 | 0.31 | 1 | 0.32 | ||
| 1005251 | 0.39 | 2.1 | |||
3. Buat SQL/query design spt dibawah ini: (sebenarnya kata kunci nya adalah) : AVG: AverageAmount([data1],[data2],[data3],[data4],[data5]) perintah module jika dipasang pada query, yakni mengambil perintah yang kita definiskan di module sebelumnya.
SELECT TableAVG.samno, TableAVG.data1, TableAVG.data2, TableAVG.data3, TableAVG.data4, TableAVG.data5, AverageAmount([data1],[data2],[data3],[data4],[data5]) AS [AVG]
FROM TableAVG;
4. Buat Report, nanti kita bermain pada textbox AVG, properties nya ber Format: standart Decimal: 3, untuk mendapatkan 3 angka dec dibelakang koma.
Hasilnya seperti ini:
Semoga bermanfaat, sekali lagi ini hanya jawabah alternatif, jikalau query tidak bisa memenuhinya, dan kelebihannya module bisa dipasang secara global sehingga bisa dipanggil untuk event berikutnya pada object lainnya.
Best Regards,
AccessWizards
From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com] On Behalf Of Rusmanto
Sent: 23 November 2010 15:30
To: belajar-access@yahoogroups.com
Subject: Re: Bls: [belajar-access] tanya rumus AVG
Maaf agak lama membalasnya soalnya lagi di luar kantor,
makasih banyak buat temen2 saya dah bisa membuat average dari tabel saya
dengan mencontoh query dari Pak Sudarsono. dan sudah berhasil
SELECT TabelAVG.samno AS Expr1, TabelAVG.N1, TabelAVG.N2, TabelAVG.N3, TabelAVG.N4, TabelAVG.N5, IIf(IsNull([N1]),0,1)+IIf(IsNull([N2]),0,1)+IIf(IsNull([N3]),0,1)+IIf(IsNull([N4]),0,1)+IIf(IsNull([N5]),0,1) AS KolomHitung, IIf(IsNull([N1]),0,[N1])+IIf(IsNull([N2]),0,[N2])+IIf(IsNull([N3]),0,[N3])+IIf(IsNull([N4]),0,[N4])+IIf(IsNull([N5]),0,[N5]) AS Jumlah, [Jumlah]/[KolomHitung] AS Rata2, Round([rata2],3) AS AuAve
FROM TabelAVG;
terimakasih banyak pak
salam
Rusman
From: sudarsono [mailto:jkssbma@yahoo.com]
To: belajar-access@yahoogroups.com
Sent: Mon, 22 Nov 2010 09:51:37 +0000
Subject: Re: Bls: [belajar-access] tanya rumus AVG
ini menggunakan Query :
SELECT [Sam No], IIf(IsNull([N1]),0,1) + IIf(IsNull([N2]),0,1) + IIf(IsNull([N3]),0,1) + IIf(IsNull([N4]),0,1) + IIf(IsNull([N5]),0,1) AS Hitung AS KolomHitung, IIf(IsNull([N1]),0,[N1])+IIf(IsNull([N2]),0,[N2])+IIf(IsNull([N3]),0,[N3])+IIf(IsNull([N4]),0,[N4])+IIf(IsNull([N5]),0,[N5]) AS Jumlah, [Jumlah]/[KolomHitung] AS Rata2, Round([rata2],3) AS Rata2des3
FROM TabelAVG;
memang sedikit panjang ya
'========salam sejahtera
'
--- In belajar-access@yahoogroups.com, Rusmanto <rusmanto@...> wrote:
>
> Terimakasih banyak temen-temen yang sudah memberikan petunjuknya,
> ini sangat membantu dan berguna buat saya dan temen2 yg lain yang mempunyai kasus yang sama.
> saya akan coba otak atik di database saya.
>
> salam
> Rusman
>
>
> _____
>
> From: Access Wizards [mailto:accesswizards@...]
> To: belajar-access@yahoogroups.com
> Sent: Mon, 22 Nov 2010 06:14:15 +0000
> Subject: RE: Bls: [belajar-access] tanya rumus AVG
>
>
>
>
>
>
>
>
>
> Bisa menggunakan module berikut untuk penghitungan average yang nilai count kosong nya tidak dihitung:
>
>
>
> Public Function AverageAmount(data1, data2, data3, data4, data5 As Variant) As Single
>
>
>
> Dim iCount As Integer
>
> Dim dblTotal As Single
>
>
>
> Args1 = Nz(data1, 0)
>
> Args2 = Nz(data2, 0)
>
> Args3 = Nz(data3, 0)
>
> Args4 = Nz(data4, 0)
>
> Args5 = Nz(data5, 0)
>
>
>
> If Args1 <> 0 Then
>
> dblTotal = dblTotal + Args1: iCount = iCount + 1
>
> Else
>
> dblTotal = dblTotal + Args1: iCount = iCount + 0
>
> End If
>
> If Args2 <> 0 Then
>
> dblTotal = dblTotal + Args2: iCount = iCount + 1
>
> Else
>
> dblTotal = dblTotal + Args2: iCount = iCount + 0
>
> End If
>
> If Args3 <> 0 Then
>
> dblTotal = dblTotal + Args3: iCount = iCount + 1
>
> Else
>
> dblTotal = dblTotal + Args3: iCount = iCount + 0
>
> End If
>
> If Args4 <> 0 Then
>
> dblTotal = dblTotal + Args4: iCount = iCount + 1
>
> Else
>
> dblTotal = dblTotal + Args4: iCount = iCount + 0
>
> End If
>
> If Args5 <> 0 Then
>
> dblTotal = dblTotal + Args5: iCount = iCount + 1
>
> Else
>
> dblTotal = dblTotal + Args5: iCount = iCount + 0
>
> End If
>
>
>
> AverageAmount = dblTotal / iCount
>
>
>
> End Function
>
>
>
> Saya coba di immediate window:
>
>
>
> ? AverageAmount(0.18,2.1,2,1.6,0)
>
> 1.47
>
> ? AverageAmount(0.31,1,0,0.32,0)
>
> 0.5433334
>
> ? AverageAmount(0.39,0,0,2.1,0)
>
> 1.245
>
>
>
>
>
>
> Best Regards,
>
> AccessWizards
>
>
>
>
>
> From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com] On Behalf Of edi rosadi
> Sent: 22 November 2010 11:53
> To: belajar-access@yahoogroups.com
> Subject: Re: Bls: [belajar-access] tanya rumus AVG
>
>
>
>
>
>
>
>
>
>
> yang dihitung hanya field yang ada nilainya. sepertinya rata-rata tidak harus dibagi banyaknya field tetapi dibagi banyaknya field yang ada nilainya. logikanya harus diselesaikan dengan menggunakan script/VBA. tidak bisa langsung menggunakan fungsi AVG. cmiiw.
>
>
> Salam,
>
> Edi Rosadi
>
>
>
> Algoritma dan Pemrograman
>
>
> Peta Industri dan Wisata Cilegon
> Blog Bahasa Cilegon
>
>
> Facebook-Perancangan Sistem
>
>
>
>
>
>
>
>
>
>
>
> komersilkan blog atau website anda dengan bergabung di sini atau di sini
>
>
>
>
>
>
>
>
>
> --- On Mon, 22/11/10, GALIH PERSADHA <galih_persadha@...> wrote:
>
>
> From: GALIH PERSADHA <galih_persadha@...>
> Subject: Bls: [belajar-access] tanya rumus AVG
> To: belajar-access@yahoogroups.com
> Date: Monday, 22 November, 2010, 10:27 AM
>
>
>
>
>
>
>
>
> sepertinya untuk yang nilainya kosong ( null ) harus berisi nol ( 0 )
>
> --- Pada Sen, 22/11/10, Rusmanto <rusmanto@...> menulis:
>
>
> Dari: Rusmanto <rusmanto@...>
> Judul: [belajar-access] tanya rumus AVG
> Kepada: belajar-access@yahoogroups.com
> Tanggal: Senin, 22 November, 2010, 8:15 AM
>
>
>
>
>
> Temen-temen saya mau tanya gimana cara buat rumus average untuk beberapa field.
>
> contoh
>
>
>
> SamNo
>
> 1
>
> 2
>
> 3
>
> 4
>
> 5
>
> AVG
>
>
> 1005151
>
> 0.18
>
> 2.1
>
> 2
>
> 1.6
>
>
>
> 1.47
>
>
> 1005201
>
> 0.31
>
> 1
>
>
>
> 0.32
>
>
>
> 0.543
>
>
> 1005251
>
> 0.39
>
>
>
>
>
> 2.1
>
>
>
> 1.245
>
>
>
> nah gimana cara buat rumus AVG nya
>
> salam
> Rusman
>
>
> _____
>
>
>
> *** 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.&nb sp;
> 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.
>
>
>
>
>
>
>
>
>
>
>
>
> *** 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.
>
*** 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.
__._,_.___
SPAM IS PROHIBITED
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
__,_._,___
No comments:
Post a Comment