Query 2:
Select Title, PublDate, LastName, PubName
From Book, Author, Publisher
Where PublDate > #11/29/2011# And
Book.AuthorCode = AuthorId And
Publisher.PubId = Book.PubId
ORDER BY PublDate DESC

Select Title, PublDate, LastName, PubName
From Book, Author, Publisher
Where PublDate > DATE() And
Book.AuthorCode = AuthorId And
Publisher.PubId = Book.PubId
ORDER BY PublDate DESC

Query 3:
Select Title, PublDate, Copyright, LastName, PubName
From Book, Author, Publisher
Where
(Copyright = 2010 OR Date()-PublDate Between 0 And 60 ) And
Book.AuthorCode = AuthorId And
Publisher.PubId = Book.PubId
ORDER BY PublDate DESC

Query 4:
Select Title, LastName, PubName
From Book, Author, Publisher
Where PubName ="Wiley" And
Title Like "*Excel 2007*"
Book.AuthorCode = AuthorId And
Publisher.PubId = Book.PubId
ORDER BY Title ASC

Query 5:
Is Null
ISNULL(fieldname)

Select Title, Edition, LastName, PubName
From Book, Author, Publisher
Where ISNULL(Edition)=True And
Book.AuthorCode = AuthorId And
Publisher.PubId = Book.PubId
ORDER BY Title ASC

within access, -1 means True
0 means false

Select Title, Edition, LastName, PubName
From Book, Author, Publisher
Where Edition Is Null And
Book.AuthorCode = AuthorId And
Publisher.PubId = Book.PubId
ORDER BY Title ASC

w3schools.com

Serial numbers in access and excel
is the offset of days since January 0, 1900
dates
times are fractions of days

Parameters in Queries
We have finished (more or less) SELECT queries

UPDATE, DELETE, DROP TABLE, CREATE TABLE
INSERT

Read ch 7 in book. quickchecks.

last time: relational database. basic SQL.
w3schools.com

SELECT Customers.CusID, First, Last, Street, Apt, City, OrderId, Descrip, Price, Qnty, [Total Price], Orders.CusID From Customers, Orders

Select * From Customers, Orders

Cartesian product of the rows of both tables

Select * From Customers, Orders
WHERE Customers.CusID = Orders.CusID

That is called an INNER JOIN. (There is also a right and a left join.)

import vs export

Layout view is WYSIWYG

Select Title, LastName, PubName
from Book, Author, Publisher
WHERE Book.AuthorCode = Author.AuthorID And
Book.PubID = Publisher.PubID And
Book.PubID = "Prentice-Hall"
ORDER BY Title

Oops!

SELECT Book.Title, Author.LastName, Publisher.PubName
FROM Publisher INNER JOIN (Author INNER JOIN Book ON Author.[AuthorID] = Book.[AuthorCode]) ON Publisher.[PubID] = Book.[PubID]
WHERE (((Publisher.PubName)="Prentice-Hall"))
ORDER BY Book.Title;

This was auto-generated by Access query designer.

Queries are dynamic

WHERE CLAUSE
ORDER BY CLAUSE

Design view

SQL View

SELECT Title,  Year, ListPrice, Publisher  FROM BOOKS
Where Publisher="Prentice Hall"

SELECT Title,  Year, ListPrice, Publisher  FROM BOOKS
Where Publisher="Prentice Hall" OR Publisher="Prentice-Hall"

SELECT Title,  Year, ListPrice, Publisher  FROM BOOKS
Where Publisher Like "Prentice?Hall"


SELECT Title,  Year, ListPrice, Publisher  FROM BOOKS
Where Publisher Like "Prentice?Hall"
Order by ListPrice DESC

Using the wizard:
1) what table?
2) what fields?
3) what to name the query?
4) go to design view, what criteria, sorting, etc. OR go to SQL view and add all that


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

*
?

Begins with G:
Like "G*"

ends with a y:
Like "*y"

contains the word Excel
*Excel*

WHERE Year >=1995 Or ListPrice>25


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

SELECT Products.[ProductName], Products.UnitPrice, Products.UnitsInStock
FROM Products
WHERE (((Products.UnitPrice) Between 50 And 100) AND ((Products.UnitsInStock)=0));

two ways of finding information from your raw data

read ch 6
quickchecks

pivottables, subtotal

Microsoft Access

Relational Database software

Database: a bunch of tables, and ways of manipulating the data in the tables

Database: a way of storing a whole bunch of data, and operations for manipulating that data

Table: a collection of records

Record: A collection of fields

Field: One unit of data

MSACCESS

is diff from Excel, Word
doesn't start you out with a blank document

You actually have to create a file

Two different views:
See the actual data in your table: Datasheet view
See the meta-data

Field Name, Data Type are properties of the field


You can have a table.

Query, Report, Form
Query: ask a question about your data
SQL
http://www.w3schools.com/sql/default.asp

Report: usually printed
don't edit the data

Form: also lets you edit. provides a user interface to the underlying table

Wildcard:
*
?

* means match anything, including nothing
? means match exactly one symbol

Works in Word, Access, Google searches

That is basic wildcard matching in Word

Advanced wildcard matching in Word
regular expressions

Go through these examples.
http://office.microsoft.com/en-us/help/add-power-to-word-searches-with-regular-expressions-HA001087305.aspx
http://office.microsoft.com/en-us/support/putting-regular-expressions-to-work-in-word-HA001087304.aspx?CTT=5&origin=HA001087305
Nothing to hand in at this time.

Walkthroughs of Excel Practice Exams

Practice Excel Exam 1.
Practice Excel Exam 2.
Practice Excel Exam 3.
Practice Excel Exam 4.
Practice Excel Exam 5.
Excel Review Presentation.


Download for Excel practice examsExam 1

Quiz today:

Based on ch 3 in the book.

Plus, material from here:

TUESDAY, OCTOBER 18, 2011

TUESDAY, OCTOBER 25, 2011

THURSDAY, OCTOBER 27, 2011

http://qccs12.blogspot.com/2011/10/no-class-this-thursday-oct-20-2011.html
http://qccs12.blogspot.com/2011/10/reading-assignment-lecture-book-ch-5-do.html
http://qccs12.blogspot.com/2011/10/we-finished-cssabsolute.html

Financial Functions

PV
FV
NPER
RATE
PMT
IPMT
PPMT


compounded monthly
nominal rate: 7% APR
7%/12
0.583% per month: periodic effective rate

why are we dividing and multiplying by 12

goal seek

Solver: goal seek on steroids

=(E12-100) *IF(E12>=100, 10%, 0%)
decision tree

nesting one if inside another if
=(E12-100) *IF(E12<100, 0%, IF(E12<1000, 10%, 15%))

Excel only lets you nest functions 7 deep

2^7 possibilities, if balance your decision tree right


decision tree were part of AI (artificial intelligence)
expert system

Financial functions
FV
PV
PMT
RATE
time value of money

future value
present value

FV = PV + PV X RATE

idea of compounding
NPER = how many compunding periods this loan is going on

FV = PV + PV X RATE
year 1 FV = PV X (1 + RATE)
year 2 FV = PV X (1 + RATE) X (1 + RATE)
year 3 FV = PV X (1 + RATE) X (1 + RATE) X (1 + RATE)
year NPER FV = PV X (1 + RATE) ^ NPER