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

      How to display data from multiple tables in a Microsoft Query.

      The following instructions can be used to set up a Microsoft Excel spreadsheet to display information dynamically linked from multiple Acctivate tables using the Microsoft Query tool. The data can be refreshed to view real-time information

      Acctivate QuickBooks Inventory Software houses an immense amount of data that is updated in real-time and that data can also be viewed in Excel as needed using the following steps. 

      NOTE: There is a simple method if you are using Microsoft Excel 2010 and need to query only a single table. Please refer to this post for instructions.

      1. Create a new spreadsheet in Microsoft Excel
      2. Start Microsoft Query
        • Excel 2003: Click New Database Query from the “Data” -> “Import External Data” menu
        • Excel 2010: On Data tab Click From Other Sources > From Microsoft Query  
      ExcelQuery

       

        • Excel 2016: On Data tab Click Get Data > From Other Sources > From Microsoft Query  

       

      3. Choose the Acctivate Data Source (ODBC) from the list.  Follow our step-by-step instructions if you haven’t set it up yet.  If you Can’t Find Created ODBC Connection you may need to also set up an additional 64bit connection. 

      ExcelQuery2

       

      ExcelQuery3

      4. Select which fields you want to display on the report. We recommend that you only use database tables that do not begin with the “tb” prefix. The internal tables use the “tb” prefix and are subject to change. Using the database views will minimize the possibility of changes to the database that require an update to the spreadsheet. 

      ExcelQuery6

       (NOTE: You may want to click the Options button and only select to show data views)

      5. Click Next

      6. You will need to join the tables if you have selected more than one table. Close the Microsoft Query window when you have finished. You will need to edit the query again to configure the remaining options.

        • Join the tables by selecting the matching fields in each table that relate the tables.
        • You can drag and drop the field from one table to the next.
        • The preferred linking method is to use the GUID columns in both tables, such as Orders.GUIDOrder and OrderDetail.GUIDOrder. However, you can use other columns to join tables such asOrders.OrderNumber and OrderDetail.OrderNumber.
        • You can double click on the line that links each table to see the advanced options. The second “Join Includes” option is usually the most common change that you will make.
        • Please contact the Acctivate support team if you have any questions about which fields should be used to properly link tables.

      7. You can filter the query by any of the selected fields. These options are similar to the report filters in Acctivate.

      8.You can sort the query by any of the selected fields. You can sort on up to three fields in the query.

      9. Choose the “Return Data to Microsoft Office Excel” option and click Finish.

      10. Click the ! toolbar button to refresh the data.

      11. Right-click on the data range and choose “Edit Query…” to modify the query.

      More Information about Microsoft Query http://office.microsoft.com/en-us/assistance/HP052747511033.aspx