Modifying existing Microsoft Queries in Excel.

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.

Acctivate QuickBooks Inventory Software was designed to automate, track, simplify and securely manage growing business needs all within the software's interface. Although, the need may arise to view real-time data for inventory, sales, purchasing, and more via Microsoft Excel, on-demand.

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” 

  3. This will open the External Data Properties window. Here, click the button next to the Name field to open the Connection Properties window.

  4. This will open the Connection Properties window. Click the Definition tab, then click the Edit Query button.

  5. From here, you should see the full Microsoft Query editor. This is where you can modify the query being returned to the spreadsheet.

    a. 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. 

    b. 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

  6. When finished, click the Return Data button to return the information to your Excel spreadsheet.

  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.