Value Added | daily

**Class Schedule**

Basics | sessions 01-05

22 AUG | intro

27 AUG | clients

29 AUG | servers

05 Sep | networks

10 Sep | basics lab

Web Development | sessions 06-11

12 Sep | structural layer

17 Sep | presentational layer

19 Sep | working with layers

24 Sep | behavior layer

26 Sep | images & design

01 Oct | website lab

Document Markup | sessions 12-14

03 Oct | object layers

08 Oct | graphics

10 Oct | document markup lab

Spreadsheets | sessions 15-19

15 Oct | spreadsheets

17 Oct | formulas & functions

22 Oct | thoughts about data display

18 Oct | Fall Break

24 Oct | database tools

29 Oct | spreadsheets lab

Relational Database | sessions 20-26

31 Oct | relational databases

05 Nov | tables

07 Nov | relationships

12 Nov | input & output

14 Nov | SQL

19 Nov | complex queries |
05.06 |
next session

26 Nov | databases lab

21 Nov | Thanksgiving

Presentation | sessions 27-30

28 Nov | presentation design

03 Dec | presentation delivery

05 Dec | presentation lab

12 Dec | 0800-1100 | final in class presentation

This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

home & schedule | class blog | syllabus | contact | grades

Build some queries that merge data from several tables

and that mathematically manipulate the data in tables.

Hey, what states are our publishers in and how many publishers are in each state?

I don't need the names of the publishers; I just need the number in each state.

But I do need to see them sorted by the number in each state with the largest number first.

Now you start to add the use of functions to manipulate the results

- this requires opening the Totals (∑) field in the QBE grid
- show a count of the number of publishers in each state
- you need to add a criteria that will eliminate all records that have no value in the state field from being counted

- count of publishers
- state

The fire insurance people need to know how much our book stocks are worth.

We need a list of the replacement costs for each our books.

They don't need to know the individual cost or the number of copies we have,

but they do want to see the replacement costs from largest to smallest,

to include the titles of the books that will need replacing.

For each book, show:

- the book title and the total monetary value of that stock of that particular book
- call this field Total Value and format it to display as currency

More functions, but instead of using a function on the results of a select query, you now you have to use the Build tool to create a new value that doesn't exist in the table as it stands

- multiply the number of each title by its retail cost and format the results to display as currency

- Total Value
- book title

What and how many books do we have on order

and how much of a break are we getting on the price?

I need to know how much of a price break we get on each book

and total savings we are getting on each order.

Show:

- the name of each book ordered
- the number of books ordered
- the price break of each book (the retail price minus the discount price) - call this field Price Break
- and the total of the savings per order (the retail price minus the discount price times the number ordered) - call this field Total Savings

This will require

- a simple select query that requires data to be pulled from several different tables and a formula that applies to two different tables
- you may need to include some fields in the query, but not show them in the results

- Total Savings
- book title
- number of books ordered
- Price Break

I need to rattle some publisher cages about slow deliveries.

Get me a list of the publishers as well as their cities and states (or countries).

Sort it by publishers because I also want to see how many of each title we have ordered from each one

and how much each order will cost if we have to buy it locally at full retail price.

This calls for values from all orders, whether already delivered or not. Show:

- the name of each publisher of the book ordered
- the name of each book ordered
- the cost of the order if it has to be purchased at the book retail price (the number of books on an order multiplied by the retail price of that particular title)
- the state and/or country for each publisher

You will need the number of books ordered, but not in the results of the query

- again, a query that calls for values from multiple tables, with an expression
- using the Totals field, don't forget to SUM the total cost, in case you have more than one order for the same book at the same price

- total cost of the order
- publisher name
- the state and/or country for each publisher
- the name of each book ordered

For no particular reason, of the books in our collection,

I would like to know what authors are published by what publishers

and how many titles each author has by that publisher.

Make it so I can do it by individual publisher.

Include in the query design:

- a non-showing parameter that will allow me to enter the publisher I wish to query
- the number of book titles associated with each author that is published by that publisher
- the name of the authors that are associated with that publisher

- number of titles shows the largest number of titles per author to the smallest number of titles per author
- author name

OK, this is the last request.

Our investments have lost value and this has cut into our budget.

We need to know where we can save some money fast.

Get a list of all our outstanding orders, ones that we have sent to the publishers,

but that the publishers have not yet filled.

We need to see how much money we have allocated for these orders so we can cancel them if we have to.

I promise, this is the last one.

This query could be the source of a report, which would present a formatted paper copy of the result of this query.

For each distinct order that has not been received yet, show only the following fields:

- the date of the order
- the book title
- the publisher name
- the discount price of the book
- the number of books ordered, and
- the total amount of money owed to the publisher for that order.
- (Call the last field Amount Owed)
- a shipment is not paid for until it is received

This calls for a select across multiple tables, newly built fields of new data, application of functions, and use of the NULL criteria. This is a request for a list of things that have not yet happened, not a list of things that have happened already.

- amount owed (from largest to least)
- publisher name
- book title
- date of the order
- discount price of the book
- number of books ordered