Programming the Data Analysis Features in Microsoft Access


Because Access does not have the breadth of data analysis features that Excel has, Access has no programmatic capabilities to do things such as insert worksheet functions or format table cells conditionally. Fortunately, the Access DoCmd object (accessible from the Access Application object) makes running most Access tasks through a program very easy. The following subroutines, included in the Chap11 folder’s BookSale.mdb file, use three of the DoCmd object’s methods: the OpenTable method opens a table, the RunCommand method runs a built-in Access command to sort data records in descending order, and the ApplyFilter method filters data records in the data table. The results of running these subroutines are shown in Figure 11-14 and Figure 11-15.

Public Sub SortBookSales() ’ Purpose: Sorts the Book Store Sales table’s records in descending ’ order by quarter. Application.DoCmd.OpenTable TableName:="Book Store Sales" Application.DoCmd.RunCommand Command:=acCmdSortDescending End Sub Public Sub FilterBookSales() ’ Purpose: Filters the Book Store Sales table’s records ’ in place so that only sales greater than $100,000 ’ are displayed. Application.DoCmd.OpenTable TableName:="Book Store Sales" Application.DoCmd.ApplyFilter WhereCondition:="Sales > 100000" End Sub

click to expand
Figure 11-14: Results of running the SortBookSales subroutine.

click to expand
Figure 11-15: Results of running the FilterBookSales subroutine.




Accessing and Analyzing Data With Microsoft Excel
Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)
ISBN: 073561895X
EAN: 2147483647
Year: 2006
Pages: 137
Authors: Paul Cornell

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net