Modifying an existing Microsoft Query in Excel

Microsoft Excel, when linked to an Acctivate database via ODBC, can be a useful tool to quickly return data from Acctivate. If you’ve created a spreadsheet with an Excel Query in the past and would like to change what data is being returned on the report, you can follow the steps below to update the query:

  1.  Open a spreadsheet in Excel that contains a working Microsoft Query
  2. Highlight cell A1 (or any cell associated with the query), then the click the Data tab and select “Properties” data properties
  3. This will open the External Data Properties window. Here, click the button next to the Name field to open the Connection Properties window.
    external data properties
  4. This will open the Connection Properties window. Click the Definition tab, then click the Edit Query button.
    connection properties window
  5. From here, you should see the full Microsoft Query editor. This is where you can modify the query being returned to the spreadsheet.microsoft query editor
    1. If you are familiar with SQL and want to run a different query, click the SQL button and replace the existing query with your new query. sql button
    2. If you would rather use the User Interface tools instead of writing  a SQL Query, click and drag field names into the table below, or go to Table > Add tables… to add a new table to the report
      add tables window
  6. When finished, click the Return Data button to return the information to your Excel spreadsheet.
    return data
  7. You can save the new spreadsheet to a different file with a new name (File > Save As), or replace the existing spreadsheet by clicking Save.
Posted in Database, Reports - Last modified on September 1, 2015Cassandra Creasy
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.