471,887 Members | 1,411 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,887 developers and data experts.

Queries in Access using SQL View

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];


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:]

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
  4. ( SELECT * FROM tblInvoice
  5.   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.

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;
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");
Some other useful articles on SQL Queries

Basic SQL Syntax for Access Queries
Subqueries in SQL
Stored Query vs SQL Statement
Nov 28 '07 #1
0 7436

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 YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.