Extracting student name and contact details example

The following example shows how to extract student names and contact details from the Synergetic database directly using Microsoft Query.
Also, see:

• Extracting data using Microsoft Query directly
• Common duplication problems.

To create a new query using Microsoft Query directly:

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 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 you plan to use. That is:
   • SynergyOne for mostly non-financial data
   • SynergyOneFinance for finance data.
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. Scroll down the list of tables until you get to the Views. These all start with a small v.
   
   
   Tip: Press v on the keyboard to position at the first view. 
   
   Note: If you only need the views to be displayed, click  and clear all but the Views field. Also, if you are using SQL 2005 you can limit the number of entries displayed by selecting dbo from the Owner drop-down list.
   
   
9. Locate and select the required view.
    
10. Click .
    The table is added to the Microsoft Query window.
    
11. Repeat steps 9 and 10 until you have finished adding tables.
    
    Tip: To add another table later, select Table > Add Tables. You may need to select a different Database, if data from the Finance database is required.
    
    Note: In this example, we are only using views that have already been created by Synergetic Management Systems. If you are using multiple tables then joins need to be made between them. Contact Synergetic Management Systems for more information. If you want to remove a table, click on it then press Delete on your keyboard.
    
    
12. Click .
13. Select Criteria > Add Criteria….
    The Add Criteria window is displayed.
    
14. Update the criteria fields.
    
    
    Note: We recommend using CurrentSemesterOnlyFlag = 1 rather than FileYear and FileSemester selections.
    
    Note: If you select a value from the Field drop-down list, you can choose from any field in the table you have added. In the wizard you can only choose from the fields that you add from the table.
    
    Tip: Click  to display all values available for this field.
    
    
15. Click .
    The criteria is added to the query window.
     
16. Select either the:
    
    • And option, if the required data set is the result of combining both selection criteria. The records must include BOTH criteria to be selected.
    
    • Or option, if the records in the required data set contain EITHER one of the selection criteria.
    
    
    
17. Repeat steps 14 through 16 until there are no more criteria to add.
18. Click .
19. Add fields to the data set. You can:
    • Double click the field from the table window.
    
    • Click and drag the field from the above window down to the grid to the column position where you want to place it.
    • Click the drop-down list on a blank column heading to display a list of all fields, then select the field you want.
    
    • Click Records > Add Column, then select the field you want and give it a heading name. Heading names of other fields can be changed by double clicking on the heading (grey area).
    The field (column) is added to your data set.
    
    
    Note: To remove a field, select the column heading as shown in the example above then press the Delete key on your keyboard. 
     
20. Select Records > Sort.
    The Sort window is displayed.
    
21. Select a field to sort on by selecting:
    • the Column drop-down list
    • either Ascending or Descending.
22. Click .
    The sort criteria is added to the Sort window.
23. Repeat steps 21 and 22 until there are no more sort selections.
    
    
    Note: The sort selections must already have been added to your query already. 
    
    
24. Review the data in the grid area.
    
    
    Note: You can adjust the height of the rows and the width of the columns so that you can view the information more fully. This is done in the same manner as in Microsoft Excel by dragging the column and row dividers .
    
    
25. Select File > Return data to Microsoft Excel.
     
26. Switch back to Microsoft Excel.
    The Import Data window is displayed.
    
27. Select the starting position on the spreadsheet.
28. Click .
    The data is retrieved and placed in the spreadsheet.
    
29. Save the spreadsheet.
    Tip: Select Data > Import External Data > Data Range (Microsoft Excel 2003) to edit additional query settings.