Quartile Functions in Accessby Emily Ediger
Microsoft Access contains powerful data storage and report creating tools, but it doesn't contain the same quartile functions as Excel. Microsoft Excel provides a full spectrum of statistical functions such as MEDIAN() and QUARTILE(). Analyze your statistical data by employing Excel for the functions, defining a custom function within Access or calling Excel functions from Access.
Quartiles help you understand the spread of your data set. The function organizes your data set from smallest to largest and determines which value is located at the 25 percent, 50 percent or 75 percent mark. Another common statistical calculation is the median, which represents the halfway point between the smallest and largest values of your data set. The second quartile is synonymous with the median, as they both locate the value at the 50 percent mark.
The simplest way to employ quartile functions on your table or query data is to export the object to Excel and employ Excel's built-in functions. Click the table or query from the Navigation Pane and select the "External Data" tab of the Ribbon. Click the "Excel" button in the Export group to open the Export Wizard. Walk through the Export Wizard by naming the workbook, maintaining the formatting and layout and selecting the file format.
After your Access table or query is exported to Excel, employ Excel's built-in quartile functions. The QUARTILE() function must contain reference to the array and the desired quartile. For example, if your data is listed in column A and you want to calculate the first quartile, input "=QUARTILE(A1:A20,1)" (without the quotation marks) into a blank cell. You can also access the quartile functions from the Formulas tab of the Ribbon by clicking "More Functions" and selecting "Statistical."
Create Access Function
You can create a custom function to employ the quartile calculation within your Access database. After you define the custom quartile function, you can use it to calculate the first, second or third quartiles in your Access object. The custom quartile function needs to define the percentile array, locate the minimum value and count the values up to the specified quartile. Remove any blank records in your query to prevent errors from occurring.
Call Excel Functions
Employ Microsoft Excel functions from the Access program to use its statistical functions. Click the "Database Tools" button and select "Visual Basic" to open the VBA window. Click the "Tools" menu, select "References" and select "Microsoft Excel Object Library" from the Available References list. Employ Excel's statistical functions from Access by calling the Excel application reference in the VBA code window. Define an array and then use the QUARTILE() function from the Excel Object Library.
- Jupiterimages/Photos.com/Getty Images