SQL Basics
Structured Query Language (SQL) is used by Office Express to interact with the underlying database. Office Express uses SQL to insert, update and delete the records in that database. SQL can also be used to ask the database questions. When you ask the database a question an "answer" will be returned.
Office Express gives you full access to SQL statements. You can keep it as simple or make it as complex as you choose. This page will give you a SQL primer that for most of you will be all you'll need to get the answers you were looking for.
Wildcard Matching
Wildcards are special characters used to match parts of a value.
Using LIKE
Use wildcards with the LIKE keyword to narrow your searches...
% - Match any occurrences of any character. For example LIKE 'SM%' would get all customers whose last name begins with SM, whereas LIKE '%SM' would locate all customers whose last name ends in SM. Finally, LIKE '%SM%' would locate any customer with SM in their name.
The Underscore (_) - Match a single occurrence of a character. For example, LIKE 'SMIT_' would locate SMITH and SMITS whereas LIKE 'SMIT%' would locate SMITH and SMITS but would also locate SMITHTOWN and SMITAMAN
Brackets [] - Defines a set of characters. For example: LIKE '[PTF]IMES' would find PINES, TIMES and FINES.
Calculated Fields
This feature allows you to perform mathematical calculations on data fields or you can combine different fields to create a new field with the joined values of the selected fields.
Concatenating Fields
This is the process of joining values together (by appending them together) to form a single long value. A good example is when creating addresses. You can join Town, State and Zip into one single field as shown below...
[Town] + ', ' + [State] + ' ' + [Zip]
Math Calculations
You can add, subtract, multiply and divide the values of different data columns.
For example, you would use a calculated field to get the Extended price of an item by multiplying the items Qty by its Price as shown below...
[Qty] * [Price] AS [Extended]
Notice the AS keyword which defines the name of the new column.
Aggregate Functions
Aggregate functions allow you to calculate the Sum, Avg, Min, Max and Count values of a data column. An example that calculates the Average of a Price column is...
SELECT AVG( [Price] ) AS AvgPrice FROM [Customer Scheduled]
Using Dates
When you want to enter date values surround the date with the pound sign (#). For example to find items after 12/1/2003 you would enter: >#12/1/2003#
NOTE: Notice the greater than sign (>) before the date in the example above. Use the less than sign (<) for dates before.
Misc. Concepts
When defining text always surround the text in single quotes. For example, 'SMITH'
Surround Data Columns in brackets. For example: [Last Name]
Want More SQL?
This page gives you just about all you need to create queries that will meet all your needs. If you are the curious type, every notable bookstore will have an SQL book in their computer section.