Page Comparison

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

...

1. Start Microsoft Excel.
2. Select:
   
   • • Data > From Other Sources > From Microsoft Query (Microsoft Excel 2010 and Microsoft Excel 2013)
   Image Added
   • 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).
   Image Removed
   
   The Choose Data Source window is displayed.
   Image RemovedImage Added
3. Select the Synergetic database you plan to use. That is:
   • SynergyOne for mostly non-financial data
   • SynergyOneFinance for finance data.
   
   Note: Entries prefixed with 'SynergyOne' are pre-Version 68 entries.
   
   
4. Clear the Use the Query Wizard to create/edit queries field.
   
5. Click .
   The SQL Server Login window is displayed.
   Image RemovedImage Added
6. Type in your username and password.
   
   Note: This is your usual Synergetic username and password.
   
   
7. Click .
   The Add Tables window is displayed.
   Image RemovedImage Added
8. Scroll down the list of tables until you get to the Views. These all start with a small v.
   Image RemovedImage Added
   
   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.
   Image RemovedImage Added 
10. Click .
    The table is added to the Microsoft Query window.
    Image RemovedImage Added
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.
    Image RemovedImage Added
14. Update the criteria fields.
    Image RemovedImage Added
    
    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 Image Removed Image Added to display all values available for this field.
    
    
15. Click Image Removed Image Added.
    The criteria is added to the query window.
    Image RemovedImage Added 
16. Select either the:
    Image RemovedImage Added
    • 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 Image Removed Image Added.
19. Add fields to the data set. You can:
    • Double click the field from the table window.
    Image RemovedImage Added
    • 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.
    Image RemovedImage Added
    • 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.
    Image RemovedImage Added
    
    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.
    Image RemovedImage Added
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.
    Image RemovedImage Added
    
    Note: The sort selections must already have been added to your query already. 
    
    
24. Review the data in the grid area.
    Image RemovedImage Added
    
    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.
    Image RemovedImage Added 
26. Switch back to Microsoft Excel.
    The Import Data window is displayed.
    Image RemovedImage Added
27. Select the starting position on the spreadsheet.
28. Click .
    The data is retrieved and placed in the spreadsheet.
    Image RemovedImage Added
29. Save the spreadsheet.
    Tip: Select Data > Import External Data > Data Range (Microsoft Excel 2003) to edit additional query settings. 
    Image RemovedImage Added