SELECT Customers.FirstName, Customers.LastName, Accounts.Balance, Branch.Location
FROM Branch INNER JOIN (Customers INNER JOIN Accounts ON Customers.[CID] = Accounts.[CID]) ON Branch.BID = Accounts.BID
WHERE (((Branch.Location)="Central"))
ORDER BY Customers.LastName;

SELECT * From Accounts, Customers

This will give me the Cartesian Product of the
two tables

SELECT * From Accounts, Customers
WHERE Accounts.CID = Customers.CID

This is called an INNER JOIN

SELECT * From Accounts, Customers, Branch
WHERE Accounts.CID = Customers.CID
AND
Branch.BID = Accounts.BID


[SalePrice]/[SqFeet] AS PricePerSqFt

as part of the field list in a Select Query

[SaleSPrice]/[SqFeet] AS PricePerSqFt

Instead of
WhERE BID="b5"

Where BID=[TheBranchIDISupply]


Serial Numbers in Excel, Access?
the way we handle dates and times
dates are whole number offsets from Jan
0, 1900

times are fractions of whole numbers

Test 2:
Query 4:
Select Title, PublDate, LastName, PubName
From Book, Author, Publisher
WHERE Book.AuthorCode = Author.AuthorID
And Book.PubID = Publisher.PubID
And PubName="Wiley"
Order By Title Asc

And PubID = "WI"

Diff types of wildcards
?
*

Begins with: G*
Ends with: *ing
Contains: *Excel 2007*

SELECT [Waxman Commissions].LastName, Sum([Waxman Commissions].SalePrice) AS Fred, Avg([Waxman Commissions].MarketDays) AS AvgOfMarketDays, Sum([Waxman Commissions].Commission) AS SumOfCommission
FROM [Waxman Commissions]
GROUP BY [Waxman Commissions].LastName;

http://office.microsoft.com/en-us/access/

Practice Access Exam Walkthrus

Practice Access Exam 1


Practice Access Exam 2

To create a query, you can use a Wizard + Design View to further modify the query.

OR, you can just write it directly in
SQL.

http://w3schools.com/sql/default.asp

SELECT * From Books

Where Clause

SELECT Title, Year, ListPrice, Publisher
From Books
Where Publisher="Prentice Hall"

wildcards
?
*


SELECT Title, Year, ListPrice, Publisher
From Books
Where Publisher Like "Prentice?Hall"
Order By ListPrice Desc

First use the wizard to choose Table(s)
and fields. Then, add sorting and criteria
in design view.

SELECT Books.Title, Books.Year, Books.ListPrice, Books.Publisher
FROM Books
WHERE (((Books.Publisher)="Prentice Hall"))
ORDER BY Books.ListPrice DESC;


Query 2:
Select Title, Author, Year, ListPrice
From Books
Where Author Like "G*"

Begins with. G*
Ends with. *ing
Contains. *Excel*

SELECT Books.[ISBN], Books.[Title], Books.[Author], Books.[Year], Books.[ListPrice]
FROM Books
WHERE (((Books.[Author]) Like "G*"));

Query 3:
Select Title, Year, ListPrice, Publisher
From Books
Where Year >= 1995 Or ListPrice > 25
Order By Year Desc

Query 4:
Select ProductName, UnitPrice, UnitsOnOrder
From Products
Where (UnitsOnOrder=0) And (UnitPrice>=50 And UnitPrice<=100)


Select ProductName, UnitPrice, UnitsOnOrder
From Products
Where UnitsOnOrder=0 And UnitPrice Between 50 100

Practice Access Exam 2.

Practice Access Exam 1.