469,604 Members | 2,358 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Queries in Access using SQL View

MMcCarthy
14,534 Expert Mod 8TB
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
0 7165

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Mike N. | last post: by
9 posts views Thread by Dom Boyce | last post: by
7 posts views Thread by Zlatko Matić | last post: by
1 post views Thread by Kurch | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.