Advanced Microsoft Query and SQL examples

Microsoft Query is a tool that is used to create SQL (Structured Query Language) statements that will extract and graphically display virtually any data that you want from the Synergetic database. Crystal Reports allows you to produce similar results but allows a lot more manipulation of the data from a graphical perspective.

Outlined below are some intermediate to advanced SQL statements that you can use with Microsoft Query. For more detailed information, refer to:

• the many good SQL websites available to assist learning this database language
• SQL Server Management Studio.

If you have access to SQL Server Management Studio (your network administrator most likely would), you can load either:

• SQL Books On-line
• Transact-SQL Help using SQL Query Analyzer.

Standard Synergetic fields used for filtering data

The following table lists fields useful for filtering data:

Example 1 - Use SUBSTRING with a character string

This example shows how to return only a portion of a character string.

From the vStudentClasses view, this query returns records filtered based on a portion of the class code.

The WHERE substring statement selects 04 because there is a character match starting from position one which includes two characters of the class code.

The following example shows you how to display the third and fourth characters of the string constant abcdef. The result set is cd.
SELECT x = SUBSTRING('abcdef', 3, 2)

Substring function

Returns part of a character, binary, text or image expression.

Syntax

SUBSTRING(expression, start, length)

Arguments

• expression is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.
• start is an integer that specifies where the substring begins.
• length is an integer that specifies the length of the substring (the number of characters to return).

Return types

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.
The returned string is the same type as the given expression with the exceptions shown in the table.

Remarks

Offsets (start and length) using the ntext, char, or varchar data types must be specified in number of characters. Offsets using the text, image, binary, or varbinary data types must be specified in number of bytes.

Note: Compatibility levels can affect return values.

Example 2 - Mail names and addresses for current parents and staff

Points to note:

• [NOT] EXISTS specifies the search for an expression, based on the expression's inclusion or exclusion from a list. The search expression can be a constant or a column name, and the list can be a set of constants or, more commonly, a sub query. Enclose the list of values in parentheses.

Example 3 - All parents who are also staff members (primary or spouse)

Points to note:

• DISTINCT specifies that only unique rows can appear in the returned data set. Null values are considered equal for the purposes of this keyword.
• LEFT JOIN specifies that all rows from the left table that are not meeting the join condition are included in the results set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
• INNER JOIN specifies that all records that meet the join condition on both sides are included in the results set.

Example 4 - Selecting records in Microsoft Query on the basis of date fields
Select enquiries in the last 7 days:

Points to note:

• The –6 means 'select all enquiries for today plus the last 7 days'.  Change the –6 to 0 for TODAY only. You can change to any other value to select enquiries from that many days ago. You MUST enter the number of days with the minus sign (means PRIOR to today).

Example 5 - Selecting students who have siblings at the school

What factor determines siblings? Answer: Primary Contact
The primary contact is the person or primary person of a couple who is the main responsible person for the child, as far as your organisation is concerned.
The child does not necessarily need to live at the address. That is the meaning of the term contact and it is normally the case.
The accounts do not need to be sent to the primary contact. That is the meaning of the debtor on the Debtor tab and again, it is normally the case.
One meaning which is attached to the primary contact is that it is the means by which Synergetic tries to work out who are siblings. That is, two students who share the same primary contact are assumed to be siblings. It does not differentiate between normal and step-siblings - that is the purpose of relationships. It is used for the following purposes:

• Determining family position. StudentFamilyPosition of 1 is always the eldest by birth date.
• Determining sibling discounts for billing debtor accounts.
• The Sibling tab in Current and Future Student Maintenance.

The following query can be used in Microsoft Query to find all future students who have a sibling who is currently enrolled, using the definition above.

Sample query

Example 6 - Select enrolment changes in the last seven days
You can also use the following statement to select records changed during the previous day(s). This statement will only work in the current year – that is, you cannot select records changed before the current year. The query uses the SQL DATEPART function to calculate the date range required.