[schedule]
[basics]
[web
pages] [markup]
[spreadsheets]
[RDBMS]
[presentations]
[UNC]
[SILS]
[REB]
[How Stuff Works]
[Langa]
[Web Style Guide]
[ATN]
[ITS Tips]
[Other Lives]
[RDBMS & tables] [relationships]
[forms & simple queries]
[more
queries & reports] [lab]
[more queries]
[subtask
5.5] [reports]
[subtask 5.6] [next
time]
[student] [topical] [radio]
[newsletter] [interest]
[policy] [special topics]
[bookmarks]
Subtask 5.5 More Query Features
- "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."
- 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 to the criteria to eliminate all records that have no value in the
state field from being counted)
- sort by state
- "The fire insurance people need to know how much our book stocks are
worth. We need a list of the replacement costs for our books."
- for each book, show the book title, the unit/book retail price,
the number of books in stock, and the total monetary value of that
stock of that particular book (call this field "Total Value" and
format it to display as currency)
- sort by "Total Value"
- 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
- this means
to multiply the number of each title by its retail cost
- "What and how many books do we have on order and how much of a break are
we getting on the price? I need the list arranged in order of the discount we
get on each book, with highest discounts first."
- show the name of each book ordered, the book retail and wholesale price, the
number of books ordered, and the price break of each book (call this field
"Price Break")
- sort by price break
- a simple select query that requires data to be pulled from
several different tables and a formula that applies to two
different tables
- "I need to rattle some publisher cages about slow deliveries. Get me a
list of the publishers and their phone numbers. I also want to see how many of
each title we have ordered from each one and how much each book costs."
- this calls for values
from all orders, whether already delivered or not
- show the name of each book ordered, the book retail price, the name of each
publisher of the book, the publisher’s phone number, the number of books
ordered, and the wholesale price of each unit of the book
- sort by the name of the Publisher and then the name of the
book
- again, a query that calls for values from multiple tables
-
"Hey, how many of each book have we ordered?"
- show the total number of units ordered of each book (e.g., if there are three
orders of Book A with 2, 3, and 4 units ordered respectively, then there are 9
copies ordered of Book A in all)
- show the book titles and the book retail price of the product in the answer to
the query
- sort in increasing order of the number of books
ordered
- multiple tables, a need to build a new field based on other fields, and a
specific sort requirement
-
"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 will be the source of your second report, which will 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 in the following order:
- the date of the order
- the book title
- the publisher name
- the wholesale 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")
- it is assumed that a shipment is not paid for until it is
received
- sort by the date of the order, then by the book title, and then by the publisher
name
- 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
[top]
