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 
