MS ACCESS MAILING LIST: Re: [belajar-access] Nilai Rata-2

Salah satu cara bisa dengan membuat function average sbb:

Option Compare Database

'Bisa menggunakan module berikut untuk penghitungan average yang nilai
count kosong nya tidak dihitung:

Public Function AverageAmount(CT1, CT2, CT3, CT4, CT5, CT6, CT7, CT8,
CT9, CT10 As Variant) As Single

Dim iCount As Integer
Dim dblTotal As Single

Args1 = Nz(CT1, 0)
Args2 = Nz(CT2, 0)
Args3 = Nz(CT3, 0)
Args4 = Nz(CT4, 0)
Args5 = Nz(CT5, 0)
Args6 = Nz(CT6, 0)
Args7 = Nz(CT7, 0)
Args8 = Nz(CT8, 0)
Args9 = Nz(CT9, 0)
Args10 = Nz(CT10, 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

If Args6 <> 0 Then
dblTotal = dblTotal + Args6: iCount = iCount + 1
Else
dblTotal = dblTotal + Args6: iCount = iCount + 0
End If

If Args7 <> 0 Then
dblTotal = dblTotal + Args7: iCount = iCount + 1
Else
dblTotal = dblTotal + Args7: iCount = iCount + 0
End If

If Args8 <> 0 Then
dblTotal = dblTotal + Args8: iCount = iCount + 1
Else
dblTotal = dblTotal + Args8: iCount = iCount + 0
End If

If Args9 <> 0 Then
dblTotal = dblTotal + Args9: iCount = iCount + 1
Else
dblTotal = dblTotal + Args9: iCount = iCount + 0
End If

If Args10 <> 0 Then
dblTotal = dblTotal + Args10: iCount = iCount + 1
Else
dblTotal = dblTotal + Args10: iCount = iCount + 0
End If

AverageAmount = dblTotal / iCount

End Function

kemudian di jalankan dengan membuat query1 sbb:

SELECT [Main Data].ID, [Main Data].DATE, [Main Data].[F-CODE], [Main
Data].CUSTOMER, [Main Data].LINE, [Main Data].PROCESS, [Main
Data].[OPERATOR NAMA], [Main Data].[C/T1], [Main Data].[C/T2], [Main
Data].[C/T3], [Main Data].[C/T4], [Main Data].[C/T5], [Main
Data].[C/T6], [Main Data].[C/T7], [Main Data].[C/T8], [Main
Data].[C/T9], [Main Data].[C/T10],
AverageAmount([C/T1],[C/T2],[C/T3],[C/T4],[C/T5],[C/T6],[C/T7],[C/T8],[C/T9],[C/T10])
AS [Rata Rata]
FROM [Main Data];

Best regards,
AccessWizards

2011/8/20, budi_secbtm@sanyo.com <budi_secbtm@sanyo.com>:
> Dear Pakar Access,
>
> Gimana cara membuat formula mencari nilai rata-2 di access? Maklum masih
> pemula
> Oya dimana ya bisa saya dapatkan contoh formula-2 yang dipakai diaccess ?
> sebab kalau pakai help yang diaccess masih kurang contoh-2 pengunaan
> formulanya
>
>
> Thank & Best Regards
> Budi
> BAT-MR
> PT. SANYO Energy Batam
> Jl. Beringin Lot 11, Batamindo Industrial Park
> Muka Kuning, Batam , Indonesia 29433
> Tel : +62-770-611321 ext 232
>
>