By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,650 Members | 1,976 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Queries in Access using SQL View

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Enter Date:] DateTime
  2. SELECT OrderID, OrderDate
  3. FROM tblOrders
  4. WHERE OrderDate = [Enter Date:]
  5.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT CustID
  2. FROM tblCustomer
  3. WHERE EXISTS
  4. ( SELECT * FROM tblInvoice
  5.   WHERE tblInvoice.CustID = tblCustomer.CustID)
  6.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMember.MemberID, tblMember.MemberName, Sum(tblFees.Fee)
  2. FROM tblMember INNER JOIN tblFees
  3. ON tblMember.MemberID = tblFees.MemberID
  4. GROUP BY tblMember.MemberID, tblMember.MemberName;
  5.  
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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(tblInvoice.InvoiceAmt) As Total Sales
  2. SELECT SalepersonID, FirstName, Surname
  3. FROM tblSalesPeople INNER JOIN tblInvoice
  4. ON tblSalesPeople.SalespersonID = tblInvoice.SalespersonID
  5. WHERE Year(tblInvoice.InvoiceDate) = Year(Now())
  6. PIVOT MonthName(Month(tblInvoice.InvoiceDate), True)
  7. IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
  8.  
Some other useful articles on SQL Queries

Basic SQL Syntax for Access Queries
SQL JOIN's
Subqueries in SQL
Stored Query vs SQL Statement
Nov 28 '07 #1
Share this Article
Share on Google+