Extracting data using the Microsoft Query wizard

The following example shows how to extract student names and contact details from the Synergetic database using the Microsoft Query wizard.

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.
Note: Entries prefixed with 'SynergyOne' are pre-Version 68 entries.
Select the Use the Query Wizard to create/edit queries field.
Click .
The Options area is displayed.
Check the database displayed in the database field to ensure you are not accidentally connecting to a pre-68 or synonym database.
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 Query Wizard - Choose Columns window is displayed.
Scroll down the list of tables until you get to the views. These all start with a small v.

Note: Only the tables and columns you have permission to access are displayed. If you need more views, contact your system administrator.

Tip: You can limit the number of Available tables and columns displayed by clicking then selecting dbo from the Owner drop-down list to show just the standard Synergetic objects.
Click the to the left of the selected view to list all fields that this view is contains. For example, vStudentContactAddress.
To select fields, highlight them then click .
Note: If you click this button while the view name is selected then all fields in this view will be added to the columns in your query. However, you rarely need to use all of the fields in a view or base table.
To follow this example, select the fields as shown in the screen capture below.
Note: Scroll down in the Available tables and columns area to find each of the fields.

Note: See the Useful fields section below.

Query Wizard - Choose Columns window

Repeat steps 10 and 11 until no more fields (columns) are required.
Click .
The Query Wizard - Filter Data window is displayed.

Tip: You can also come back to this screen at any time by clicking while you are still using the wizard.
Select the field (column) to filter on and the selection criteria. For example, you can enter the following selection criteria, CurrentSemesterOnlyFlag equals 1, to retrieve records for the current year and semester. In the following example, the first selection criteria set is FileYear equals 2006.

Tip: If the view contains the CurrentSemesterOnly flag, you can use this instead of FileYear and FileSemester fields to select the File Year and Semester defaults you have defined in your preferences.

Note: When you choose your filter the Column Name becomes bold, this will let you know what fields you have already applied filters to.
Click .
The Query Wizard - Sort Order window is displayed.
Select the field to base the sorting data on. You can choose multiple fields to sort by but Surname is usually a good option.
For example, click the drop-down list and select Surname. Leave the sort type as Ascending.

Note: Ascending order is A-Z, descending order is Z-A.
Click .
The Query Wizard - Finish window is displayed.
Either click:
- , if you plan to reuse this query at a later date.
The Save As window is displayed.
Name the query using a suitable name, such as vStudentContactAddress.
Click .
The Query Wizard - Finish window is redisplayed.
Select Return Data to Microsoft Office Excel.
Click .
Switch back to Microsoft Excel.
The Import Data window is displayed.
Select the starting position on the spreadsheet.
Click .
The data is retrieved and placed in the spreadsheet.
Save the spreadsheet.
You can edit the query in one of two ways:
- Editing the query using the wizard. See Editing a Microsoft Query using the wizard.
- Editing the query directly using the Query window. See Extracting student name and contact details example.
  Tip: Select Data >Import External Data > Data Range (Microsoft Excel 2003) to edit additional query settings.

Useful fields

The following fields are useful when querying Current Student views using the Microsoft Query wizard:

For information on other useful fields and generated fields, see Synergetic views.

Field	Description
CurrentSemesterOnlyFlag	Only view data from the current year and semester. The default value in this field changes depending on the user’s Synergetic preferences. It is preferable to use the CurrentSemesterOnlyFlag rather than FileYear and FileSemester fields.
FileYear	Use to set the current year, to avoid using information from previous or future years.
FileSemester	Use to set the current semester, to avoid using information from previous or future semesters. For example, when using FileYear and FileSemester: If using the same query three years from the date of creation, you will need to reset these filters to match the current year and semester. These selections would only be useful if you want to access prior years on the database.
ContactAddressFull	Gets the contact’s address and organises it in a mailing format such as: Synergetic Management Systems Building 4, 303-313 Burwood Hwy Burwood East VIC 3151
StudentContactAddressComma	Places commas in the address, used for listing names and addresses. For example: Synergetic Management Systems, Building 4, 303-313 Burwood Hwy, Burwood East VIC 3151 These fields are constructed from the Address1-3, Suburb, State, PostCode and Country. They also take into account whether the current person is deceased or the address is no longer valid on the system.
StudentContactMailName	Defined by the MailFormat in Community Maintenance, this field is normally in the format of Contact Title and Contact Spouse Title Contact First Initial Surname. For example, Mr and Mrs P Bailey.
StudentContactMailSalutation	Organises the ContactMailName in the format you have specified within Synergetic’s Community Maintenance, MailSalutation. For example, you can remove the contact’s initial.
Surname	Used for sorting purposes. You can later delete the field but keep the sorting. If you are using the wizard then you need to add it now.
StudentName	Student’s Name and Surname joined together. It will be used later when doing a Mail Merge within Word. If you want only the student’s preferred name then you may need to join to another table or select a different field in the view. For example, Brian Smith.
EntryDate / LeavingDate	The EntryDate and LeavingDate are used together to filter out students who are not at your organisation at a certain date. All students should have an entry date, after which date they are present at your organisation, unless they have left.

Last modified: 20/07/2017 1:38:18 PM