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.

what would a database system look like if it were NOT relational

problems
1) a lot of repeated data
2) inconsistent data

related tables

CustID on the Customer Table is a primary key
uniquely identifies a record

OrderID on the Orders Table is a primary key
uniquely identifies a record

benefits of a relational database:
1) smaller database, no repeated data
2) consistent data

BillCustId and ShipCustID are foreign keys
match up with primary key of another table

Indexing
What if I have 1 million records and I want to search for "Josh Waxman" as the name? How long will it take, on average, to find that record?

Why will it take 5 minutes? Sequential search.
(n+1)*(n/2)
(n^2 +n)/2
divide by n to get average
(n+1)/2 is the average

with 1 million records, sequential search will take (n+1)/2, or 500,000 time units, on average

within the CS department, there is a course called Analysis of Algorithms. Algorithm: a recipe for solving a problem.

Binary search would be much faster. But needs items to be in sorted order.

It is like the high-low game.
mid = (top + bottom)/2
if num looking for is more:
  top = mid + 1

if num looking for is less:
  bottom = mid - 1

we can either have a sorted list Or we can have Binary Search Tree. Course in CS dept called Data Structures. considers how to store collections of data.

if indexed, instead of O(n), it will take O(log n), or 20 searches for a million records.

we are skipping ch 1 hands on 2. we are on page 581, ch 1 hands on 3.

Quiz next time we meet; also Excel exam

The quiz on Ch4 in lecture book.
from SUNDAY, OCTOBER 23, 2011 until
SUNDAY, NOVEMBER 13, 2011

Read, do quickchecks of Ch 6

lecture

Goal Seek
within the PMT function

I can fiddle with some numbers

Solver -- more advanced -- goal seek on steroids

Solver can fiddle with multiple cells
Solver can solve for a maximum or minimum
Solver can work within constraints

Pivot Table
Pivot Chart

CSV file = comma separated value

difference between raw data and information
PivotTables and PivtoChartsCharts are more dynamic than Subtotal

Filtering and Advanced Filtering

in advanced filtering, within same row, it means AND
within diff rows, means OR

Financial Formulas

1) What function should I use?
FV, PV, PMT, RATE, NPER

2) Once I determine that, what should be the parameters

Wildcards:
*
?

J*sh
______
Josh
Jish
Jash
Joosh
Jsh

but not
Joshua

What about J?sh
____
Josh
Jish
Jash
Not Joosh
Not Jsh


Microsoft Access requires an actual file to work against. diff from word and excel

Many changes to your Access file cannot be undone.
Many changed to your Access file are automatically saved.

RDBS - relational database system
store your data, has ways of processing, viewing, asking questions about your data

Database: collection of Tables
Table: collection of records
Record: collection of fields
Field: one unit of information