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 (*) |
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:
- Start Microsoft Excel.
- 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. - Select the Synergetic database.
- Clear the Use the Query Wizard to create/edit queries field.
- Click .
The SQL Server Login window is displayed. - Type in your username and password.
Note: This is your usual Synergetic username and password. - Click .
The Add Tables window is displayed. - Click .
- Click .
The SQL window is displayed. - Switch to the application with the SQL statement, such as an email message.
- Select the SQL statement text and press Ctrl + C.
The SQL statement is copied to the clipboard. - Switch back to the SQL window.
- Press Ctrl + V.
The SQL statement is pasted into the SQL window.
- Click .
The Microsoft Query window is updated with the new criteria and the records are displayed. - 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.