Microsoft Access is a Visual Basic based application that allows the SQL statements to be embedded in VBA code and macros. One of the most used SQL statements, the SELECT statement, provides the much needed flexibility in the retrieval of data from a database. The general form of the SELECT command is:
SELECT select_list FROM source [ WHERE condition(s) ] [ GROUP BY expression ] [ HAVING condition ] [ ORDER BY expression ] ;
The select statement, like all other SQL statements, should end with a semi-colon. The semi-colon terminates a statement. If you wanted to select an entire table, every record and every field, the command would be:
SELECT * FROM source; or SELECT ALL FROM source;
The absence of the WHERE clause signifies that there are no restrictions to be placed on the rows, so display all rows. This query statement can be further modified to indicate specific fields (or columns) and specific records (or rows) to retrieve, and also set limitations on, the queried results using the WHERE clause. The absence of the GROUP BY, HAVING and ORDER BY clauses signifies that there are no particulars restrictions to be put on the output of the query.
The optional conditions may make use of comparison operators, which include the following:
= | Equal To |
> | Greater Than |
< | Less Than |
>= | Greater Than or Equal To |
<= | Less Than or Equal To |
<> | Not Equal To |
LIKE | String Comparison Test |
Aggregate functions can be included to perform computations on numeric values. Aggregate functions include the following:
MIN | Returns the smallest value in a given column |
MAX | Returns the largest value in a given column |
SUM | Returns the sum of the numeric values in a given column |
AVG | Returns the average value of a given column |
COUNT | Returns the total number of values in a given column |
COUNT(*) | Returns the number of rows in a table |