A Guide to Advanced SQL - Microsoft Access

1

, 2




1. General Form of a SQL SELECT Statement


SELECT TOP <n> <field(s)>
FROM <tableName(s)>
WHERE <condition(s)>
GROUP BY <expression>
HAVING <condition>
ORDER BY <expression>

SELECT Extracts data from the table (Fields that need to be displayed)

TOP <n> Limits the number of rows displayed in a table

FROM Found in the specified table(s)

WHERE ● Will select all records where the field(s) condition(s) are met
<condition> ● Condition can consist of any Boolean condition, using any number of fields
● A field value can be compared to a value, but not to an aggregate
The condition always consists of:
● A field name, followed by a relational operator, then a comparison value

GROUP BY Provides the result of a (function) for each (category) of rows
<expression> Used to assign values to a specific field - keyword ‘each’

HAVING Removes rows from a GROUP BY Result Set
<condition> (Same way as a WHERE clause, but is used for aggregate functions → COUNT( ),
SUM( ) etc.)

ORDER BY Ranks selected fields only, in order of the fields entered (field1 before field2)
<expression>



Whenever there is a primary / foreign key in one table that links to a foreign key (a primary key in
another table) a relationship exists between the two tables and we can perform queries that make use
of that relationship to combine data from both tables

The primary / foreign key relationships that exist in this database are:

● ActNum primary key in tblActs links to the foreign key ActNum in tblEntrants
- the relationship is one-to-many, where one act may have many entrants

● ActNum primary key in tblActs links to the foreign key ActNum in tblPoints
- the relationship is one-to-one, meaning that one act has one set of points

When there is a one-to-one relationship between primary keys of two tables, the primary key of each
table is considered to be a foreign key in the other table




2