Simple Excel Query using Query Wizard

The Microsoft Query Wizard is an easier way to quickly create a Microsoft Query right from Excel.

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.

  1. First, you must Establish the ODBC Database Connection.
    • Users who are hosted may may need to contact the hosting provider for assistance.
  2. Next, create your Microsoft Excel Query.

In Excel:

  1. Open Excel
  2. Select the Data tab
  3. Choose Get Data > From Other Sources > From Microsoft Query 

Choose Data Source:

  1. Select the Acctivate Data Source
  2. To use Query Wizard make sure the option Is Checked
  3. Click OK
    •  
  4. Enter SQL Server Login information
  5.  Click  “Options” to expand the default database options
  6. Select the drop down for Database and choose your Acctivate Database
  7. Click OK

Query Wizard

  1. Choose Options
  2. Select Views
  3. Select List Tables and Columns in alphabetical order
  4. Choose Owner ‘dbo’
  5. Click OK
    •    

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.

  1. Scroll Down and Find the ‘CustomerEmailAddressList’ View
  2. 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
  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
  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

Congratulations you just created your simple Excel Query using Query Wizard!  You now have a Customer Email List in Excel from Acctivate.

 

If you get stuck and need help, you can contact Acctivate Support here.

 

Posted in Database, Reports - Last modified on November 9, 2018Eric Cartrite
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.