Introduction to Access SQL

Introduction
SQL commands
Example Database
Looking at the SQL of a query
Looking at the SQL of a lookup field
Looking at the SQL of a combo box
Looking at the SQL of a graph Simple selection


Example 1 - query using the Sort option and Top Values
Example 2 - query using multiple tables
Example 3 - query using selection criteria

Operators

Example 04 - query using criteria in different fields
Example 05 - query using criteria in same fields
Example 06 - query using Like
Example 07 - testing for unknown values

Creating a parameter query

Example 08 - prompted query

Calculations in queries

Example 09 - using aggregate functions
Calculations on groups of records
Example 10 - calculation on a group
Example 11 - setting criteria on aggregate queries

Arithmetic expressions

Example 12 - query using arithmetic
Example 13 - unique values
Example 14 - character function

The iif function

Example 15 - iif
Example 16 - group iif

Date handling

Example 17 - dates
Example 18 - date calculation
Example 19 - range of values

Queries within queries

Example 20 - nested query

Calculating percentages

Example 21 - calculating a percentage

Unmatched query

Example 22 - unmatched query

Union query
Action queries
Creating and listing tables
Table Constraints
Indexes
Data Dictionary
SQL Syntax

Introduction

This document aims to describe the SQL (Structured Query Language) generated by Microsoft Access when using the QBE interface. By looking at different types of queries, one can teach oneself SQL. This document aims to describe the purpose of each SQL clause and how it all fits together.

Although most of the time one need not be exposed to SQL in Access it can be useful to understand the structure of SQL and when it generates clauses and extra brackets unexpectedly. It is also useful to know how Access SQL differs from standard SQL, in case one is transferring the database to another package such as Oracle.

Knowing about SQL in Access is useful when:

• Looking at the source of a combo box since it is given in SQL- one could also change the ordering or add a conditional clause.
• Looking at the source of a graph since it is given in SQL- one also could add a conditional clause or copy a graph and change the base table or query.
• If a query is not doing what is expected then maybe looking at the SQL may be more obvious.
• When one wants to create similar queries based on different tables it can be easier to copy the original query, and change the name of the table using the SQL view and edit using notepad or wordpad (Access does not provide an editor, just a zoom facility) since changing the name of the table using the grid can mean that all the carefully constructed fields vanish if you are not careful.
• One wants to do a union query. This is a special type of query than can only be constructed if one knows SQL. An example is shown later.

SQL commands

SQL commands are used to create, query and maintain a database. A query can contain only a single SQL command. The command is terminated by a semicolon.

Retrieving data from the database to display is the most common SQL operation, and to perform this the select command is used. The basic select command has two parts (clauses):

The select clause is always entered first, and is immediately followed by the from clause. It can also specify conditions for selecting records, ordering, grouping and joins between tables. A Full list of SQL clauses is given at the end of this document

Example database

The example database used in this document has the following 5 tables:

Patients
Drugs
Doctors
Treatment
Diagnosis

The data consist of details of each patient (date of birth, sex etc), doctor, drugs available and details of treatment. The patients are identified by a reference number (PatientlD). By splitting the data like this, duplication of details such as drug name is avoided (see also Access query language elements (document acc-r4).

Looking at the SQL of a query

To see the SQL that is generated when using Access queries:

• Open a query in design view
• select SQL from the view menu

Looking at the SQL of a table lookup box

• Open a table in design view
• select the field that has a lookup attached to it. For example if you are using the treatments example database, look at the PatientID field of the Treatment table.
• press the lookup tab below the list of fieldnames
• Look at the Row source Property:
  SELECT DISTINCTROW [Patients].[PatientID], [Patients].[Given name], [Patients].[Family name]
  FROM [Patients];

Looking at the SQL of a combo box or listbox

• Open a form in design view (for example Combo 2 autolookup in the treatment example database).
• select the field that has a lookup attached to it.
• select the data properties
• Look at the Row source Property:
  SELECT DISTINCTROW DRugID, [Drug Name]
  FROM Drugs
  ORDER BY [Drug Name];

Looking at the SQL of a graph

• Open a chart form in design view (for example drug pie chart in the treatment example database).
• click on the graph
• select the data properties
• Look at the Row source Property:
  SELECT DISTINCTROW [Drug name], SUM([all_info].[Treatment].[PatientID]) AS [Treatment_PatientID]
  FROM [all_info]
  GROUP BY [Drug name];

Example 1 - query using the Sort option and Top Values

List all the details of all the patients in ascending order of family name:

• Look at the SQL:
SELECT Patients.*
FROM Patients
ORDER BY Patients.[Family name];

• SQL is case-insensitive- it does not matter whether it is typed in upper or lower case.
• An SQL select command can have many clauses- which must be specified in a particular order. As a minimum, it must specify which fields are to be selected -
  the select clause (the fields are separated by commas; * means all fields.
  the from clause must also specify which table(s) they are to be taken from.
• The order by clause specifies which fields govern the order in which the records are displayed
• SQL commands can continue over more than one line in a query. They are terminated by a semicolon in a query.
• Fieldnames and tablenames which include a space or arithmetic operator etc will have the name enclosed by square brackets to show where the name begins and ends.
• The fieldnames are preceded by the tablename by default (for example Patients.*, Patients.[Family name]. In actual fact the field names only need to prefixed by the table name if the same field name appears in a query involving more than one table and the name of the field is in both tables.
• Look at the properties of the query by right-clicking the area where the tables are displayed. By default, Unique Records and Unique Values are both set to No. If one changes Unique Records to Yes, and then looks at the SQL, DISTINCTROW will be added to the select clause. Note that DISTINCTROW may be added automatically to the SQL (particularly in combo boxes), this indicates that data based on duplicate entire records will not be displayed more than once. In practise, if one has set a primary key, there will be no duplicate records. Data duplicated on selected fields will however still be displayed. For example, if one is just displaying the gender field, m and f will be displayed as many times as there are patients Example 13 shows how to display distinct values on selected fields (just show m and f once).
• To select the first 5 patients, click on the design button to return to the query design window, then use the drop down box on the Top Values button to select the first 5 patients. By default the value shown is All.
SELECT DISTINCTROW TOP 5 Patients.*
FROM Patients
ORDER BY Patients.[Family name];

Creating a query using more than one table

Example 2 - query using multiple tables

Find which drugs were taken by which gender:

SQL:
SELECT Patients.Gender, Treatment.Dosage, Drugs.[Drug name]
FROM Drugs
INNER JOIN (Patients INNER JOIN Treatment ON Patients.PatientID = Treatment.PatientID) ON Drugs.DrugID = Treatment.DrugID;
• When data from more than one table is selected by a query, the tables must be joined using the common linking fields. In this example, data is selected from the DRUGS, PATIENT and the TREATMENT table. In this case the common linking fields are PatientlD and DrugID which is shown by the inner join clause.
• the inner join clause has come from linking the tables by drawing a line between the two tables. It signifies an equi-join (to only display the combined record if there are matching fields in both records). This is a non-standard SQL clause to link the two tables. If one wanted to use standard SQL, a where condition would be added not by drawing a line between the linking fields, but by using the criteria box instead
  eg in the TREATMENT.DrugID field specification add the criteria [DRUGS].[DugID]. This results in a where clause instead of an inner join clause. The where clause must appear after the from clause. You must add the square brackets else Access assumes that you are looking for text and helpfully adds double quotes!
  where [Patients].[PatientID] = [Treatment].[PatientID]
  and [Drugs].[DrugID] = [Treatment].[DrugID]
• It is more efficient to specify the join conditions before any other conditions in the where clause.
• If the field name is unique to a particular table (for example 'Drug name'), the table name need not be specified. If the field name appears in more than one table being queried, it must be prefixed by the table name.

Selection criteria

The Criteria and Or field options are used to specify whether you want the query to include or exclude -information based on the contents of a field. Note that Access inserts hashes (#) around dates -this confirms that Access recognises them as date strings (see date handling). Quotes are automatically placed around the text strings. Numeric criteria do not have quotes or hashes around the number. Field names are enclosed by square brackets.

Example 3 - query using selection criteria

Find the female patient born on 22 November l942 whose ID is less than or equal to 10.

SQL:
SELECT Patients.PatientID, Patients.Title, Patients.[Family name],
Patients.Gender, Patients.[Date of Birth]

FROM Patients
WHERE (((Patients.PatientID)<=10)
AND ((Patients.Gender)="f")
AND ((Patients.[Date of Birth])=#11/22/42#));


• A where clause is included in the select command after the from clause to choose only records which fit specified criteria.
• Note that Access has a habit of adding large numbers of brackets.
• Square brackets are used to determine field names and table names eg [Date of Birth] since including spaces and operators is allowed for names (but not advisable!)
• Round brackets are used around each where condition to determine order of precedence, and around functions eg avg(Fees)
• If no operator is given in the where criteria then "=" is used.
• You can use <, <=, >, >= , <> and "between…..and..…" on text, date or numeric fields.
• Text searches are not case sensitive.
• The and operator between two criteria indicates that both criteria must be true.
• Quotes are placed automatically round text to be searched (double by default but one can use single quotes).
• Date values need to be surrounded by #. This enables the user to type the date in whatever format is most familiar and it will then be converted into a form that Access can handle.
• Numeric criteria do not need quotes or a hash around the number.

Operators

Operators can be used to construct selection criteria for queries. Operators are:

• Extra examples showing use of operators are given in the database.
  SQL:
  SELECT Drugs.[Drug name], Drugs.[Tablets per packet],
  
  Drugs.[Cost per packet]
  
  FROM Drugs
  WHERE (((Drugs.[Drug name])>"d")
  AND ((Drugs.[Tablets per packet])>50));
  
  SELECT Patients.Initials, Patients.[Family name],
  
  Patients.[Date of Birth], Patients.PatientID
  
  FROM Patients
  WHERE (((Patients.[Family name]) Between "b" And "l")
  
  AND ((Patients.[Date of Birth]) Between #1/1/60# And #12/31/69#)
  AND ((Patients.PatientID) Between 10 And 20));

Example 04 - query using criteria in different fields

Find patients who live in Bristol or who are female:

SQL:
SELECT Patients.[Given name], Patients.[Family name], Patients.Town, Patients.Gender
FROM Patients
WHERE (((Patients.Town)="bristol"))
OR (((Patients.Gender)="f"));


• The or operator includes all entries where one or the other (or both) of the conditions is true. Round parentheses may be used to indicate order of precedence particularly when and and or conditions are used together.

Example 05 - query using criteria in the same fields

Find patients who live in Bristol or Bath:

SQL:
SELECT Patients.[Given name], Patients.[Family name], Patients.Town,

Patients.Gender
FROM Patients
WHERE (((Patients.Town)="bristol"))
OR (((Patients.Town)="bath"));


• Note that the grid will automatically change the 2 criteria on separate fields (since it is the same field) to 1 criterion on town as in the former query, but the SQL ends up with more round brackets since it puts them round each criteria field.
• You could type in ("Bristol","Bath") instead of the two where conditions
  
  SELECT Patients.[Given name], Patients.[Family name], Patients.Town
  FROM Patients
  WHERE (((Patients.Town) In ("bristol","bath","keynsham")));
• The in operator gives a list of possible values for a field. In this example, it is shorter to use in than oring the conditions as above.

Example 06 - query using Like

Find the patients whose name starts with "m" and postcode starts with B and then any letter between A and S then a single digit and a space:

SQL:
SELECT Patients.Initials, Patients.[Family name], Patients.Postcode
FROM Patients
WHERE (((Patients.[Family name]) Like "m*")

AND ((Patients.Postcode) Like "B[A-S]# *"));
• Typing a wild card character such as * introduces the "like" operator. Access will change the criteria m* to like "m*"

• Like allows comparisons between strings using wild card characters and so is different to "=".
  • * represents any number of any character
  • ? represents a single character
  • # represents a single digit
  • [A - S] any character between A and S
  • W[ae*] - starting with W followed by a or e then any other characters
  • W[!ae]* -starting with W and not followed by a or e.

Example 07 - testing for unknown values

List the patients with no town given.

SQL:
SELECT Patients.PatientID, Patients.[Family name], Patients.Town
FROM Patients
WHERE (((Patients.Town) Is Null));
• Is null is used to test for an unknown value. Unknown values are distinguished from 0, so when averages etc are calculated, any blank values are ignored.

Creating a parameter query

A parameter query (or prompted query) is a type of select query which is useful if you frequently run the same query but change the criteria each time you run it. Once the query is created you can re-run it without opening the query window or making changes in the QBE grid; instead Access prompts you for criteria in the Enter Parameter Value dialog box. You can specify as many criteria as you want. (Parameter queries are particularly good when used as filters with forms.)

Example 08 - prompted query

List the patients for a given town:

• The prompt is enclosed in square brackets, for instance "Which town?" (This should not be the name of a field otherwise Access compares with the value of that field).

SQL:
SELECT Patients.Initials, Patients.[Given name], Patients.[Family name], Patients.Town
FROM Patients
WHERE (((Patients.[Family name]) Like [first few letters of surname] & "*")

AND ((Patients.Town)=[which town]));
• Note the parameter names enclosed in square brackets just like the fieldnames which have spaces as part of the name

Calculations in queries

You can perform calculations on groups of records using queries called aggregate queries. Expressions are typed in the Field box instead of fieldnames. The functions available for these queries include: Sum, Avg, Min, Max, Count, StDev, First and Last. Domain aggregate functions (name starts with 'd'), dsum, davg etc are used when calculating statistics over a subset of values on a form or report, not a select query. See Help/aggregate functions/calculate d fields.

Example 09 - using aggregate functions

To find the average number of tablets and the most in a packet of drugs using the Drugs table:

SQL:
SELECT Avg([tablets per packet]) AS [average no of tablets],

Max([Tablets per packet]) AS [most in packet]
FROM Drugs;
• Note that the parameters of functions are enclosed in parentheses (). The names of fields with a space in them are enclosed in square brackets []. Note also that you only get one row of results since they are being aggregated.
• Notice that the field headings (aliases) can be changed (using the as clause, maximum length 64 characters) to improve the appearance of output (up to a limit of 30 characters) by preceding the field by the heading and a colon.
• get error message invalid bracketing of name if the heading is too large.
• Aggregate functions operate on all the selected records, but exclude values from any fields with a null value (that is, have an unknown value). This includes the Count function. Demonstrate this by counting town and patientID on the patients table (this gives the number of patient records which have a value for the town).
• An extra example is given in the database to show the use of domain aggregate functions.

Calculations on groups of records

It is possible to run a query that calculates values for each group of records for example, for the patients who all come from the same town. To do this you need to specify the fields to use with the GroupBy option, and the fields to use f or the aggregates by specifying a calculation (Sum or Avg for instance).

Example 10 - calculation on a group

Find how many patients there are in each town:

SQL:
SELECT Patients.Town,
Count(Patients.PatientID) AS [Number of patients],
Count(Patients.Town) AS CountOfTown

FROM Patients
GROUP BY Patients.Town
ORDER BY Count(Patients.PatientID) DESC ,
Count(Patients.Town) DESC;
• The group by field option on the Town field operates with the Count function on the patient ID to divide all the patients into groups based on the town in which they live. Count is then applied to the rows in each group. Note that some patients do not have a town as part of their address. Count should be applied on the Primary key (PatientID) to give the number of patients for each town including those patients where the town is unknown. Applying the count on town shows 0 for unknown towns.
• The order by clause orders output, in ascending order by default. It is possible to order on a count as well a field. If the field to be ordered has an alias, the alias can be given in the order by clause.
• The order by clause must follow the group by clause.
• note that the order clause has been added when the sort field has been chosen
• order is always the last clause of a select statement (unless the with option has been used)
• The asc and desc operators specify whether the ordering is ascending or descending. The default is asc.
• If the ordering is to be done on more than one field, the field specifications are separated by commas.

Example 11 - setting criteria on aggregate queries

Find the towns whose name start with B and which have more than 3 patients. Display in order of size:

SQL:
SELECT Patients.Town,
Count(Patients.PatientID) AS count
FROM Patients
WHERE (((Patients.Town) Like "B*"))
GROUP BY Patients.Town, Patients.Town
HAVING (((Count(Patients.PatientID))>3))
ORDER BY Count(Patients.PatientID) DESC , Patients.Town;
• The having subclause of the group clause limits the number of groups used.
• The having criteria should normally be used on the aggregate function fields such as avg, sum etc since it works on the aggregate value not the individual record value unlike the where clause
• It is important to distinguish the difference between testing for a field value and an aggregated value. This demonstrates the use of the where option to perform criteria on individual field values, where it is used to filter out records before aggregation ie towns whose name start with B. The having criteria checks the result of an aggregated function ie more than 3 patients. Do NOT put town like "B*" in the having clause but in the where clause. Although putting the criteria in the having clause will give the correct answer it is very inefficient since the query has to select all the records before it aggregates them and then filter out the towns not starting with B. Putting the criteria in the where clause makes sure that the non-B records are filtered out before the aggregation is done so it has far less work to do.
• the group clause always appears after the where clause (or from clause if there is not a where clause
• having clause always appears after the group clause and cannot be used without a group by clause
• order by can be used on aggregate functions as well as field values

Arithmetic expressions

You can use arithmetic expressions in queries (+, -, * (multiplication) and / (division)). Note that if one of the fields used for multiplying and dividing has a null value or has a null value then the result of the whole expression will b e null.

Example 12 - query using arithmetic

Find the cost per tablet of each drug:

SQL:
SELECT Drugs.[Drug name], Drugs.[Tablets per packet],

Drugs.[Cost per packet],
[Cost per packet]/[tablets per packet] AS cost_per_tablet
FROM Drugs
WHERE (((Drugs.[Tablets per packet])>0));
• Arithmetic expressions can be used in the select as well as the where clause.
The cost per tablet for each drug is calculated by dividing the cost per packet by the number of tablets per packet.

Example 13 - unique values

Find the different towns that patients come from:

SQL:
SELECT DISTINCT Patients.Town
FROM Patients;
• DISTINCTROW means the whole of the record has to have distinct values regardless of whether they are being displayed whereas DISTINCT displays unique values

Example 14 - character function

Display the towns that people come from in capital letters:

SQL:
SELECT DISTINCT UCase([Town]) AS towns
FROM Patients;
• The ucase function converts values to upper case
• Access changes the name of the function from ucase to UCase but case has no significance so it does not matter how you type it
• Remember to name the heading of the field differently to the name of the field otherwise you get a circular alias message.

The iif function

You can also use iif to return more meaningful values than are stored in the database, and to do calculations based on the field value.

iif requires the name of the field or an expression, followed by the criteria, and the values to be returned if true and false. In the example below, if gender is m, "male" is returned, otherwise "female" is returned.

If you use a mathematical operator to find a value and one of the fields is blank, the result is a null value. To prevent this you can use the iif function to check and produce an error message, for instance if a date is missing from a field used in a calculation, the message might ask you to check for a missing date. A new function in Access 97 is nz which may also be used.

Example 15 - iif

Use iif to expand the values "m" and "f" to "male" and "female" in the output:

SQL:
SELECT IIf([Gender]='m','male','female') AS gendername,

Patients.Gender, Patients.PatientID
FROM Patients;

Example 16 - group iif

Find, for each drug, the dosage by each gender:

In this example the clause: iif(gender,"m", dosage, null) selects the dosage if gender = "m"; otherwise the value is set to the null value and is not included in the calculation of the average.

SQL:
SELECT Drugs.[Drug name],
Avg(IIf([Gender]='m',[dosage],Null)) AS maledose,
Avg(IIf([Gender]='f',[dosage],Null)) AS femaledose
FROM Drugs
INNER JOIN (Patients INNER JOIN Treatment ON Patients.PatientID = Treatment.PatientID)
ON Drugs.DrugID = Treatment.DrugID
GROUP BY Drugs.[Drug name];
• This type of query calculates an average on records then groups the result by two different ways - one down the left side (the group by clause) and the other across the top (the aggregated functions).

• The field aliases Maledose and Femaledose are used to specify the field names.

WHERE ((Not (Drugs.DrugID)
=Exists (select * from Treatment where Drugs.DrugID = Treatment.DrugID)));

not in method

SELECT Drugs.DrugID, Drugs.[Drug name]
FROM Drugs
WHERE drugid not in (select drugid from Treatment );
• The not in method is very inefficient as no indexes are used

Union Query

SQL:
SELECT Patients.PatientID, [Patients].[Given name] & " " & [Patients].[Family name] AS patient, [Doctors].[Title] & " " & [Doctors].[Family name] AS doctor,
Diagnosis.[Date seen] as date_happening, Diagnosis.diagnosis

FROM Patients INNER JOIN (Doctors INNER JOIN Diagnosis ON Doctors.DoctorID = Diagnosis.DoctorID) ON Patients.PatientID = Diagnosis.PatientID
UNION
SELECT Patients.PatientID, [Patients].[Given name] & " " & [Patients].[Family name] AS patient, [Doctors].[Title] & " " & [Doctors].[Family name] AS doctor,
Treatment.[Date start course] as date_happening,Drugs.[Drug name]

FROM Doctors INNER JOIN (Drugs INNER JOIN (Patients INNER JOIN Treatment ON Patients.PatientID = Treatment.PatientID) ON Drugs.DrugID = Treatment.DrugID) ON (Doctors.DoctorID = Treatment.DoctorID) AND (Doctors.DoctorID = Treatment.DoctorID)
ORDER BY Patients.PatientID, date_happening;
• Union combines the rows returned by two select statements.
• The number of fields in the two select clauses must be the same, and the data types of the selected fields must match.
• The as clause can be used to change the name of the resulting fields. By default, the resulting field be the name of the field of the first slect clause. Thus, the last field is called diagnosis, the penultimate field is called date_happening

Action queries

Action queries enable you to make large numbers of similar changes to a database easily. There are 3 types of action query:

Creating and listing tables

Microsoft Access tables are normally built using Table button, but one can create a table using SQL by creating a new query, not adding any tables. You must select 'SQL specific' from the 'Query' menu' Then choose 'data definition'. Note that the datatypes are given as: text, datetime, byte, short, integer, single, double, binary, boolean, currency

The SQL command create table creates a table. For example, to create the example database tables:

• The maximum length of Access table and field names is 64 characters. You are recommended for efficiency to use fewer than 10 characters.

• The maximum number of fields in an Access table is 255.

• The names of tables are given in capitals by convention; in fact, case is ignored except in text strings. Note that spaces may not be used in field names when creating tables in this way.

• Access does not distinguish between names typed in different cases and is very bad at checking for reserved words. name is not an Access reserved word but which can cause problems if a field has that name. Look at the Access help file for res erved words
• not null specifies that every entry in the table must have a value for that field.

• In Drugs, each 'Drug' is defined to be unique and thus is designate to be the primary key (fields used to uniquely identify a table). More than one field may form the primary key.

  DRUGS and TREATMENT are related by the field drugid. drugid in TREATMENT is the foreign key (one or more fields whose values are based on the primary key from another table and used to specify the relationship between two tables).

  Value constraint (which for example ensures that the field can contain only values between 1 and 4) can not be added in Access SQL

  Table Constraints

  If you want to add constraints later using SQL you can alter the table definition using SQL Specific from the Query menu.

  alter table DRUGS add constraint DRUGS_pk primary key (drugid)

  alter table TREATMENT add constraint TREATMENT_fk foreign key(drugid) references DRUGS(drugid)

  The 'references' clause shows the relationship between the DRUGS and TREATMENT tables.

  Note that referential constraints are normally added by editing the relationships diagram in Access, and field validity is added by editing the properties of the table.

  Any query, form or report built can incorporate these constraints automatically, provided they were defined before the query, form or report was built.

  The foreign key ensures that records added to the treatment table will relate to a known Drug. If you add a foreign key, it must be the same as the primary key of the related table and the primary key clause must have been used on that table definition .

  Indexes

  If you want to add indexes later using SQL you can use SQL Specific from the Query menu.

  Tables can have any number of indexes. There are two reasons for creating an index:

  • creating an index based on fields which are to be used in relating one table to another, or a field which is queried frequently, makes accessing the table faster.
  • a unique index ensures that the rows can be uniquely identified using the fields specified (you would normally use the 'primary key' clause). A table must have a primary key if it is to have other tables linked to it.

  create unique index TREATMENT_IND on TREATMENT (drugid);

  Data Dictionary

  The data dictionary is used to find out what tables etc have been created. The data dictionary is populated automatically and can not be updated by the user.

  Note that Access does not provide a proper data dictionary. If Options is selected from the View menu and system objects is set to yes, then several extra tables are listed whose names start with MSys, for example MSysObjects, and these can be examined.

  Tools/Analyze Documentor is also very useful

  Standard SQL syntax

  SELECT [predicate] { * | table.* | [table.]field1 [, [table.]field2.[, ...]]}
  [AS alias1 [, alias2 [, ...]]]
  FROM tableexpression [, ...] [IN externaldatabase]
  [WHERE... ]
  [GROUP BY... ]
  [HAVING... ]
  [ORDER BY... ]

  where predicate is any or distinct

  Access SQL syntax

  [TRANSFORM]aggfn(cell value)
  SELECT [predicate] { * | table.* | [table.]field1 [, [table.]field2.[, ...]]}
  [AS alias1 [, alias2 [, ...]]]
  FROM tableexpression [, ...] [IN externaldatabase]
  [INNER JOIN table 2 on table1=table2.field1 and table2 on table1.field2=tabl2.field2...]
  [RIGHT JOIN table 2 on table1=table2.field1 and table2 on table1.field2=tabl2.field2...]
  [LEFT JOIN table 2 on table1=table2.field1 and table2 on table1.field2=tabl2.field2...]
  [WHERE... ]
  [GROUP BY... ]
  [HAVING... ]
  [ORDER BY... ]
  [PIVOT fieldheading]
  [WITH OWNERACCESS OPTION]

  predicate are all, distinct, distinctrow, top (eg select top 10 gives first 10 records)

  WITH OWNERACCESS OPTION gives the user in a multiuser environment, permission to view the data in a query even if the user is otherwise restricted from viewing the query's underlying tables.

A 4 table join:
SELECT .....
FROM Doctors INNER JOIN


(Drugs INNER JOIN

(Patients INNER JOIN
Treatment
ON Patients.PatientID = Treatment.PatientID)
ON Drugs.DrugID = Treatment.DrugID)
ON Doctors.DoctorID = Treatment.DoctorID;

---

Using Access queries - acc97-r5 (06.99)

This document may be used wholly or in part by the academic community, providing suitable acknowledgement is made. It may not be used for any commercial or profit-making purpose without permission.
© 1999 University of Bristol Computing Service. All rights reserved.

---