Database
      Back to home
      1. Knowledge Base
      2. Learn
      3. Database

      Creating a Microsoft Query from Excel.

      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.

      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, make sure Microsoft Query is added in Excel's settings.
      3. Create your Microsoft Excel Query.

       

      In Excel:

      1. Open Excel
      2. Select File > Options
      3. Select the Data section
      4. In the Show legacy data import wizards section, check the box next to From Microsoft Query (Legacy)



      5. Click OK
      6. Select the Data tab
      7. Choose Get Data > Legacy Wizards > From Microsoft Query (Legacy)

       

      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.