Inserting an existing SQL statement

Sometimes you may have a query that has already been perfected on another system. You can either:

• Open the query file that has been provided or saved with a .dqy extension.

  

• Paste a SQL statement into the SQL window on the Microsoft Query window.

  

In this example, we will follow the process when someone from the Synergetic Management Systems team emails you a SQL statement. This Structured Query Language (SQL) statement is written in a language that database management systems can interpret. Synergetic uses the Microsoft SQL Server database management system.

In this example, we will use a simple SQL statement that will report and total all religion codes at your organisation:

This statement selects campus, religion code and places a count field on your spreadsheet. The fields:

• to be returned or calculated are placed directly after the SELECT clause
• used to filter the records are placed directly after the WHERE clause
• used to group the output are placed after the GROUP BY clause
• used to sort the information are placed after the ORDER BY clause.

To paste in a SQL statement that has been sent to you by email:

1. Start Microsoft Excel.
2. Select:
   • Data > From Other Sources > From Microsoft Query (Microsoft Excel 2010)
   • Data > Import External Data > New Database Query (Microsoft Excel 2007)
   • Data > Import > Import External Data > New Database Query (Microsoft Excel 2003)
   • Data > Get External Data > Create New Query (Microsoft Excel 97).

   

   The Choose Data Source window is displayed.

   

3. Select the Synergetic database.
4. Clear the Use the Query Wizard to create/edit queries field.

   

5. Click .

   The SQL Server Login window is displayed.

   

6. Type in your username and password.

   Note: This is your usual Synergetic username and password.

7. Click .

   The Add Tables window is displayed.

   

8. Click .
9. Click .

   The SQL window is displayed.

   

10. Switch to the application with the SQL statement, such as an email message.
11. Select the SQL statement text and press Ctrl + C.

    

    The SQL statement is copied to the clipboard.

12. Switch back to the SQL window.
13. Press Ctrl + V.

    The SQL statement is pasted into the SQL window.

    

14. Click .

    The Microsoft Query window is updated with the new criteria and the records are displayed.

15. You can:
    • select File > Save to save the query for later use

      

    • select File > Return Data to Microsoft Office Excel to return the data to a spreadsheet.

      

Last modified: 26/05/2017 5:15:04 PM

© 2017 Synergetic Management Systems. Published 20 July 2017.