This article explains an easy way to create a Microsoft Query from Excel.
The Microsoft Query Wizard is an easier way to quickly create a Microsoft Query right from Excel to easy access Acctivate QuickBooks Inventory Management data. To view our in-depth webinar on Excel Query, see this page.
The basic steps to setup a Microsoft Query using the Query Wizard are below, these may vary slightly depending on your version of Microsoft Excel.
- First, you must Establish the ODBC Database Connection. Users who are hosted may may need to contact the hosting provider for assistance.
- Next, create your Microsoft Excel Query.
In Excel:
- Open Excel
- Select the Data tab
- Choose Get Data > From Other Sources > From Microsoft Query
![](https://help.acctivate.com/wp-content/uploads/Excel_2018-11-08_15-31-31.png)
Choose Data Source:
- Select the Acctivate Data Source
- To use Query Wizard make sure the option Is Checked
- Click OK
![](https://help.acctivate.com/wp-content/uploads/ChooseDataSource-1.png)
4. Enter SQL Server Login information
- User Name: 'sa'
- Password: (Contact Acctivate Support)
5. Click "Options" to expand the default database options
6. Select the drop down for Database and choose your Acctivate Database
7. Click OK
![](https://help.acctivate.com/wp-content/uploads/SQLServerLogin-1.png)
Query Wizard
- Choose Options
- Select Views
- Select List Tables and Columns in alphabetical order
- Choose Owner 'dbo'
- Click OK
![](https://help.acctivate.com/wp-content/uploads/QueryWizard_Options.png)
![](https://help.acctivate.com/wp-content/uploads/Table_Options.png)
Select Database Views
Now you are ready to select the Views and Fields you want to return to Excel. You can run Excel Queries from any workstation that has a database connection configured.
For Example let's say you want a Customer Email Address List.
- Scroll Down and Find the 'CustomerEmailAddressList' View
- To select 'All Fields' in the view
- Select the view and click the (>) right arrow
- This will move all all columns to your query
3. To select 'Specific Fields' (Only the data you want to display)
- Click the (+) plus to expand to see available fields
- Select the field you want to display and click the (>) right arrow
4.When finished Click Next
![](https://help.acctivate.com/wp-content/uploads/ChooseColumns.png)
5. The next screen will allow you to Filter by Branch, Customer Type, Sales Person or some other field that you have selected
- Select the field you want to use as a filter
- Choose the criteria of the filter
- Click Next
6. Then you can select the order to Sort order for the display
- Choose how you want to the data sorted when it is returned to Excel
- Click Next
![](https://help.acctivate.com/wp-content/uploads/SortOrder.png)
7. Now you are ready to Finish and Display in Excel
- Click Finish to Import Data
- Select the field in Excel where you want the table to start
- Click OK
![](https://help.acctivate.com/wp-content/uploads/Import_Data.png)
Congratulations you just created your simple Excel Query using Query Wizard! You now have a Customer Email List in Excel from Acctivate.
![](https://help.acctivate.com/wp-content/uploads/CustomerEmailList.png)