The Microsoft Office Access database program includes a set of aggregate functions, allowing users to perform mathematical calculations. These include averages, counting the number of items in a column and finding the minimum or maximum value in a column of data. You can use these functions in the database queries or in Visual Basic for Applications (VBA) code for the calculation of a single value, such as a sum or column average. The column must contain numeric, currency or date/time data. You need to define a range of data in order to calculate the average of a column in VBA program.
- Skill level:
Other People Are Reading
Click the "Start" button in Windows and select "Microsoft Office," "Microsoft Visual Basic for Applications."
Click "Open," "New" to create the VBA script for calculating a column average in your Access database.
Enter the following code in the window:
Dim Rng As Range
Dim i As Long
Set Rng = Range("B1:B3")
For i = 3 To 12
Cells(i, "C").Value = ColumnFunction.Round _ (ColumnFunction.Sum(Rng) / 3, 0) Set Rng = Rng.Offset(1, 0)
Click "File," "Save" to save the VBA script for calculating an average of the "B" column.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for