SQL Quickly

SQL (Sturctured Query Language) is a standard language for querying and modifying relational databases. It is an ANSI and ISO standard, although various vendors have added proprietary extensions. It is beyond the scope of this document to describe SQL or the differences between Microsoft Access SQL and ANSI SQL. However, examples of SQL queries are provided in this document as a tutorial. Most users of Access probably use the graphical design view for queries, but SQL queries are better suited for examples. These queries can by typed or copied and pasted into the Access query SQL view. The query can then be executed or opened in design view to show the graphical representation. One difference between Access SQL and other flavors is the wildcard; Access uses * rather than %.

SQL Example

The following SQL example lists the number of sites by GeoPoliticalID (the name of the country) for and GeoPoliticalID that is defined as a country.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SELECT
        COUNT(sites.SiteName),
        gpu.GeoPoliticalName,
        gpu.GeoPoliticalUnit
FROM
        (
                SELECT
                        *
                FROM
                        GeoPoliticalUnits
                WHERE
                        geopoliticalunits.GeoPoliticalUnit = "country"
        ) AS gpu
INNER JOIN (
        Sites
        INNER JOIN SiteGeoPolitical ON Sites.SiteID = SiteGeoPolitical.SiteID
) ON gpu.GeoPoliticalID = SiteGeoPolitical.GeoPoliticalID
GROUP BY
        gpu.GeoPoliticalID,
        gpu.GeoPoliticalUnit;

Table Keys

Within tables there are often Keys. A Key may be a Primary Key (PK), which acts as a unique identifier for individual records within a table, or they may be a Foreign Key (FK) which refers to a unique identifier in another table. Primary Keys and Foreign Keys are critical to join tables in a SQL query. In the above example we can see that the

Data Types

In the table descriptions in the following section, the SQL Server data types are given for field descriptions. The equivalent Access data types are given in the following table.

SQL Server data type Access data type
bit Yes/No
datetime Date/Time
float Double
int Long Integer
nvarchar(n), where n = 1 to 4000 Text
nvarchar(MAX) Memo