Inserting an existing SQL statement

Owned by Former user (Deleted)
Last updated: Mar 03, 2020 by Richard Kennedy (Unlicensed)

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:

Query

SELECT vStudents.StudentCampus, vStudents.StudentReligionCode, Count (*)
FROM dbo.vStudents vStudents
WHERE (vStudents.CurrentSemesterOnlyFlag=1)
GROUP BY vStudents.StudentCampus, vStudents.StudentReligionCode
ORDER BY vStudents.StudentCampus, vStudents.StudentReligionCode

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 and Microsoft Excel 2013)

    • Data > Import External Data > New Database Query (Microsoft Excel 2007)
    • Data > 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.