Queries in Access using SQL View

[MMcCarthy]

Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access.

Angle brackets <> are used in place of some syntax elements you must supply. The description of these elements will be in the contained in the angle brackets.

Square brackets [] are used to show which parts are optional.

Basic SELECT query

SELECT <field list>
FROM <table/query name(s)>
[WHERE <set critieria on field list>]
[GROUP BY <fields to group values on>]
[HAVING <set criteria on grouped fields>]
[ORDER BY <fields to sort by> ASC/DESC];

Parameters

Goes before a SQL statement to define the data types of any parameters you include in a query. This is useful when you are asking the user to input values or matching values in controls on an open form.

PARAMETERS [Enter Date:] DateTime
SELECT OrderID, OrderDate
FROM tblOrders
WHERE OrderDate = [Enter Date:]
 

Exists

You can use this keyword to test whether you can return values that satisfy selection in a subquery. The following query will return all customer who have been issued an invoice.

SELECT CustID
FROM tblCustomer
WHERE EXISTS
( SELECT * FROM tblInvoice
  WHERE tblInvoice.CustID = tblCustomer.CustID)
 

Aggregate Functions

Creating SQL statements which include aggregate functions like Avg(), Count(), Sum(), etc. usually requires the GROUP BY clause.

If you place an aggregate function on a field then all fields returned by the query must be included in an aggregate function or a GROUP BY clause. The following query will return the total amount of fees paid by a member.

SELECT tblMember.MemberID, tblMember.MemberName, Sum(tblFees.Fee)
FROM tblMember INNER JOIN tblFees
ON tblMember.MemberID = tblFees.MemberID
GROUP BY tblMember.MemberID, tblMember.MemberName;
 

If you include any other fields in the above query (in the GROUP BY clause) then the sum of the fees would be broken out over that field as well.

Crosstab Queries

Crosstab queries can be used to summarise a single value using the values in another column and using other column(s) to define the grouping by rows. The syntax is:

TRANSFORM <expression using total function>
<SELECT Statement>
PIVOT <field to summarise on>
[IN <value list>]

For example, to get the total sales per salesperson by month you can do the following:

TRANSFORM Sum(tblInvoice.InvoiceAmt) As Total Sales
SELECT SalepersonID, FirstName, Surname
FROM tblSalesPeople INNER JOIN tblInvoice
ON tblSalesPeople.SalespersonID = tblInvoice.SalespersonID
WHERE Year(tblInvoice.InvoiceDate) = Year(Now())
PIVOT MonthName(Month(tblInvoice.InvoiceDate), True)
IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
 

Some other useful articles on SQL Queries

Basic SQL Syntax for Access Queries
SQL JOIN's
Subqueries in SQL
Stored Query vs SQL Statement