How to Use Excel in Access
Microsoft Excel is used primarily for accounting and financial operations, while Microsoft Access is used as a repository or database to store information.
However, Microsoft Excel and Microsoft Access are very compatible. This means that Microsoft Excel information can be imported into Microsoft Access and Microsoft Access information can be exported to Microsoft Excel. This also means that functions used in Microsoft Excel can be used in Microsoft Access.
- Open Microsoft Access 2007 Northwind Database.
- Create a new query using the Query Wizard. Select “Simple Query Wizard”. Navigate to “Table: Order Details” to enter the order table details in the query.
- Click on the “Unit Price” field to enter it in the query. Click “Next” (to produce a Detailed Query). Select the “Change the Design of this Query” option, then click “Finish.” This will open the request in Design view.
- Right-click on “Field: Unit Price” and click on “Build”; This opens the “Expression Builder” dialog box. Type “Sum” before “[UnitPrice]”. The expression must read as follows: Sum ([UnitPrice]). Press OK.
- Run the query. Total in query is the sum of all inventory unit prices.
The main difference between the formulas used in Microsoft Excel and Microsoft Access lies in the abbreviation. For example, the average function used in Excel is spelled “Average,” while the average function used in Access is “average”. Functions that can be used in Microsoft Access are available in the “Expression Builder” dialog box under “Functions”> “Built-in Functions.” Most of the Excel functions used in Access will be entered into the query. Functions in Excel are equivalent to expressions in Access.