/
Inserting an existing SQL statement

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:

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.
     


Related content

Extracting student name and contact details example
Extracting student name and contact details example
More like this
Report mode
More like this
Advanced Microsoft Query and SQL examples
Advanced Microsoft Query and SQL examples
More like this
Extracting data using the Microsoft Query wizard
Extracting data using the Microsoft Query wizard
More like this
Importing results from Microsoft Excel into a column
Importing results from Microsoft Excel into a column
More like this
Importing results from Excel
Importing results from Excel
More like this