Example 1

Figure 6-4. Numbers table

Figure 6-4 shows a table named Numbers. Suppose you want to add two values that are stored in two separate columns and multiply a value that is stored in a column by a specified value. Look at the following script:

SELECT (ColumnOne + ColumnTwo) AS AddColumns,
(ColumnThree * 2) AS MultiplyByTwo
FROM Numbers;

The preceding script uses the plus (+) operator to add the ColumnOne column to the ColumnTwo column. The result of the addition is displayed under an alternate name (AddColumns). Next, the multiply (*) operator is used to multiply the ColumnThree column by the value of 2. The result of the multiplication is also displayed under an alternate column name (MultiplyByTwo). Figure 6-5 shows the results from the query.

Figure 6-5. Results (output)

Example 2

Suppose you want to retrieve records that are based on a WHERE clause that contains a comparison operator and an arithmetic operator. Look at the following:

SELECT ColumnOne, ColumnThree
FROM Numbers
WHERE ColumnOne < ColumnThree + 4;

The preceding query displays the ColumnOne and ColumnThree columns. The WHERE clause sets a criterion to only retrieve records where the first column (ColumnOne) is less than the result of Column Three added to the number four (ColumnThree + 4). Look at the results in Figure 6-6.

Figure 6-6. Results (output)

Example 3

Suppose you want to use the Numbers table in Figure 6-4 to average the values stored in the ColumnOne column, find the first and last values stored in the ColumnOne column, sum the values stored in the ColumnTwo column, and find the highest and lowest values stored in the ColumnTwo column. Take a look at the following script:

SELECT AVG (ColumnOne) AS Average, FIRST (ColumnOne) AS FirstValue,
LAST (ColumnOne) AS LastValue, SUM (ColumnTwo) AS Summed,
MAX (ColumnTwo) AS Highest, MIN (ColumnTwo) AS Lowest
FROM Numbers;

The preceding script uses the AVG (ColumnOne) function to average the values in the ColumnOne column. Notice that the name of the column is enclosed within the parentheses of the AVG () function. The FIRST (ColumnOne) and LAST (ColumnOne) functions are used to find the first and last values that are stored in the ColumnOne column.

The SUM (ColumnTwo) function is used to sum the values stored in the ColumnTwo column. The MAX (ColumnTwo) and MIN (ColumnTwo) functions are used to find the highest and lowest values stored in the ColumnTwo column. Figure 6-7 shows the results from the query.

Figure 6-7. Results (output)

Example 4

The Numbers table in Figure 6-8 has been altered slightly from the original Numbers table in Figure 6-4. The Numbers table in Figure 6-8 contains two NULL values. Notice that there is a value missing in the ColumnOne and ColumnThree columns.

Suppose you want to use the Numbers table in Figure 6-8 to count the rows in the table and the rows in the ColumnThree column. Look at the following script:

SELECT COUNT (*) AS TableCount,
COUNT (ColumnThree) AS ColumnCount
FROM Numbers;

The preceding script uses the COUNT (*) function to count the total number of rows in the Numbers table, including NULL values. The COUNT (ColumnThree) function is used to count the total number of rows in the ColumnThree column, excluding NULL values. Look at Figure 6-9 to see the results.

Figure 6-9. Results (output)

Sometimes you will see a column added to a query where the SELECT statement includes SUM (1) AS TableCount. This is the equivalent of COUNT (*)

Example 5

The following script uses the ampersand (&) to merge the Lastname column with a comma and the Firstname column with one space and a slash. The plus sign (+) is used to merge an empty string with the Lastname column and a comma, and to merge the slash and space with the Areacode column. The AS keyword is used to create an alias (NamesAndAreacodes).

SELECT Lastname & ','+''+Firstname&'/'+Areacode AS
       NamesAndAreacodes
FROM Committee2;

Look at the results in Figure 6-10.

Figure 6-10. Results (output)

Example 6

Figure 6-11. Computers table

Suppose you want to query the Computers table in Figure 6-11. You want to retrieve the first two characters/numbers from each value stored in the SerialNum column. Additionally, you want to display all the values in the Brand column in all uppercase letters, display the total number of characters for each value in the Department column, and trim any existing and trailing spaces from the values stored in the OfficeNumber column. Look at the following script:

SELECT LEFT (SerialNum, 2) AS FirstTwoChars,
UCASE (Brand) AS Uppercase, LEN (Department) AS TotalChars,
TRIM (OfficeNumber) AS TrimSpaces
FROM Computers;

The preceding script uses the LEFT (SerialNum, 2) function to return the first two leftmost characters from the values stored in the SerialNum column. The column name (SerialNum) specifies which column to return data from and the number two (2) specifies how many characters to return. The UCASE (Brand) function is used to return all the values in the Brand column converted to uppercase. The LEN (Department) function is used to return the total number of characters for each value stored in the Department column. Finally, the TRIM (OfficeNumber) function is used to remove leading and trailing spaces from the values stored in the OfficeNumber column. Figure 6-12 shows the results from the query.

Figure 6-12. Results (output)

Example 7

Figure 6-13. Computers table

Suppose you want to query the Computers table in Figure 6-13 to retrieve the first five characters/numbers from each value stored in the SerialNum column. You also want to display the numeric position of the first occurrence of the number 2 in each value stored in the SerialNum column. Look at the following script:

SELECT MID (SerialNum, 1, 5) AS FirstFiveChars,
INSTR (1, SerialNum, 2) AS PositionOfTwos
FROM Computers;

The preceding script uses the MID (SerialNum, 1, 5) function to retrieve the first five characters/numbers from each value stored in the SerialNum column. The column name (SerialNum) represents the column from which to retrieve characters/numbers. The 1 represents the character position in the SerialNum column at which the part to be returned begins. The 5 represents the number of characters to return.

The INSTR (1, SerialNum, 2) function is used to display the numeric position of the first occurrence of the number 2 in each value stored in the SerialNum column. The 1 represents the numeric expression that sets the starting position for reading the values stored in the SourceString (SerialNum column). The column name (SerialNum) represents the column to search, and the 2 represents the string expression being sought. Look at the results in Figure 6-14.

Figure 6-14. Results (output)

One of the more important features of the INSTR function is that the function returns specific values depending upon whether or not the searched for string is in the source string. Refer to Table 6-5.

Example 8

Figure 6-15. Activities table

Suppose you want to insert a new record containing dates into the Activities table in Figure 6-15. Look at the following script:

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (8, ' Remotivation therapy', #01/01/08#, #01/31/08#);

The above script inserts four values into the Activities table. Two of the values are dates. Although Microsoft Access stores dates as numbers, dates must be enclosed in pound signs. Figure 6-16 shows the addition of the new record. The dates are all aligned to the right because, like numeric fields, all date values are aligned to the right by default.

Figure 6-16. Results (output)

Example 9

Suppose you want to retrieve the start dates in a different format than they appear in the Activities table in Figure 6-15. Look at the following script:

SELECT ActivityName, FORMAT (StartDate, 'mmmm-dd-yyyy') AS BeginDate
FROM Activities;

The above script uses a date format (mmmm-dd-yyyy) in the FORMAT () function that enables you to spell out the full name of the month, the two-digit day, and the full four-digit year. Table 6-7 displays the date formats available in Microsoft Access’s SQL view. As you can see, there are an almost unlimited number of ways to format date and time.

Figure 6-17 displays the results from the query in Example 9.

Figure 6-17. Results (output)

Example 10

Say you want to display the current date and time. Additionally, you want to display the ending dates of all activities in the Activities table with the month, day, and year displayed in separate columns. Take a look at the following script:

SELECT DATE () AS TodaysDate, TIME () AS CurrentTime, MONTH
(EndDate) AS EndDateMonth, DAY (EndDate) AS EndDateDay, YEAR
(EndDate) AS EndDateYear
FROM Activities;

The above script uses the DATE () and TIME () functions to display the current system date and time. The MONTH (EndDate) function is used to display the numeric representation of the month from the date stored in the EndDate field. The DAY (EndDate) function is used to display the numeric representation of the day from the date stored in the EndDate field. The YEAR (EndDate) function displays the numeric representation of the year from the date stored in the EndDate field. Figure 6-18 displays the results.

Figure 6-18. Results (output)

Example 11

Figure 6-19. Numbers table

For our last example, let’s take a look at one of the functions (CCUR ()) listed in the miscellaneous functions table above.

The CCUR () function is used to convert a value to currency. Take a look at the following query, which demonstrates the use of the CCUR () function:

SELECT ColumnOne, CCUR (ColumnOne + 2.00) AS
TwoDollarIncrease
FROM Numbers
WHERE ColumnOne IN (10, 70, 90);

The preceding SQL script queries the Numbers table in Figure 6-19. The WHERE clause specifies a condition for three records (10, 70, 90). The query retrieves the ColumnOne column and the ColumnOne column added to two dollars. The CCUR () function is used to display the two dollar increase as currency. Notice the dollar sign in the TwoDollarIncrease column in Figure 6-20.

Figure 6-20. Results (output)