When you are working with a client or a subordinate or someone else that is not familiar with database terminology, they might not give you short and clear instructions to help you get the data they need. So these questions have to crafted in a way that is a bit challenging for you to follow. I would suggest that you first read from the bottom up. This is a concept called beginning with the end in mind.
More Complex Queries
qry09
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.
One table, three values (but only two will show) - two from a field, one created from a field with a totals criteria
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
Sort the results in this order:
count of publishers
state
qry10
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.
One table, two values - one from a field, one created with a built function
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
Sort the results in this order:
Total Value
book title
qry11
11. 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.
Two tables, four values - two from a field, two created with a built function
Show:
the name of each book ordered
the number of books ordered
the price break of each book - call this field Price Break
and the total of the savings per order - 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
Two most important sort results should be in this order:
Total Savings
book title
number of books ordered
Price Break
qry12
12. 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.
Three tables, four values - three from a field, one created with a built function
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. (Optional: Add a separate order for the same book to see this in action.)
Sort the results in this order:
total cost of the order
publisher name
the state and/or country for each publisher
the name of each book ordered
qry13
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.
Four tables, three values (of which only two will show) - two from a field, one with a totals criteria
Include in the query design:
a non-showing parameter that will allow me to enter the publisher I want to query; make this so that I can enter any part of the publisher name
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
Sort the results in this order:
number of titles shows the largest number of titles per author to the smallest number of titles per author
author name
qry14
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.
Data from two tables, seven values (of which only six will show) - six from a field, one that has to be calculated
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 utilizing more than two tables, even though data only comes from two tables.(look at your relationship view to see why). You will also need to build fields of new data, apply of functions, and use 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.
Sort the results in this order:
amount owed (from largest to least)
publisher name
book title
date of the order
discount price of the book
number of books ordered
This is the critical value. It does not show and does not need to be sorted.