MS Excel
Microsoft Excel is an excellent analysis tool that can be used to create
charts, pivot tables, and more, using data retrieved from any Omnidex
enhanced database.
Excel uses Microsoft Query behind the scenes to create a query and return
the data to Excel for analysis.
Please see the Prerequisites
before beginning.
 This discussion
is not intended to teach MS Excel.
Get External Data
To begin, run Microsoft Excel and open the Excel spreadsheet that you will
import the data into. This can be a new spreadsheet or an existing one.
- In the menu bar, select Data, Import External Data (Get External Data
in Excel 2000 or older), and New Database Query.
- A Choose Data Source
dialog appears. The data sources displayed may vary, depending on the
version of Microsoft Office installed. If the data source you want to
connect to is a file data source and you don't see it displayed in the
list, click Browse to locate it. Select the Omnidex data source that
you wish to connect to and click OK.
- A connection is made and a list of available
tables is displayed. To select
the columns that will be returned in the query, expand a table by
clicking the +, then double click the column name or click the greater
than ( > ) sign to select an individual column, or click the double
greater than ( >> ) sign to select all columns in the selected
table. When finished selecting all of the desired columns, click Next.
- The Filter Data dialog is displayed. In this dialog, you can apply
criteria to any of the columns selected in the previous step. Click
on a column in the list at the left, then select a comparison operator
from the drop down list in the center, then enter the criteria value
in the text box to the right.
To apply criteria to another
column, repeat this process. When finished adding criteria, click
Next.
- The Sort Order dialog is displayed.
If desired, select the column or columns to sort by in the drop down
list to the left and click Next.
- The next dialog asks where
to return the data to. Select the first option, Return Data to Microsoft
Office Excel and click Finish.
- The last dialog asks
where in Excel should the data be placed. The default is to start in
cell A1 in the current worksheet. Change this location if desired then
click OK.
- The data is then placed into the
spreadsheet starting in the location indicated in the previous step.
This is a copy of the data, therefore, any changes to the data will
not affect the original data.
You may now manipulate the data using any of Excel's functions and abilities.
Changes to
the data in Excel will not be reflected in the underlying database.
Multiple Tables
If you would like to retrieve columns from multiple tables in the same
query, you must first create and save the query in Microsoft Query with
the tables joined. Excel does not have the ability to join the tables
for you.
Then, in Excel, select Data \ Import External Data \ Import Data from
the menu. Browse to find the saved query, it should have a .dqy file extension,
and click Open.
Top
|
