Task 05.05
Create each of the following queries.
Save each using the qryNN naming convention, with the actual question appended if you wish
- for example, the first query should be saved as qry01
- or, if you wish, as qry01:Where are our publishers located?
For the queries in this task,
- all sorts based on text are ascending
- all sorts based on numbers are descending
Be sure to put a sort on every value that you use in a query, but pay attention to the order in which the query will sort the values.
The queries below follow a pattern
A question, or a query, that you might hear asked
followed by some thoughts you should consider as you create the query
[top]
Create each of the following queries.
01. Get me a list of our publishers and, oh yes, I need to know where they are located.
One table, three values
Show the names of all of the publishers, their states and/or their countries.
Sort the results in this order:
- country
- state
- name of the publisher
[top]
02. We want to spread around our purchases. If I tell you a state, can you tell me what publishers we deal with in that state?
One table, two values, one with a parameter criteria
This is a question about US publishers, so use a criteria on the country field to weed out any publishers who may not have a location in a US state and a parameter criteria on the state field to select the specific state
- the parameter should permit the user to enter the state abbreviation in the query, remembering that the states are stored in two letter abbreviations
- use your parameter instructions to guide the user to understand what values to enter into the parameter dialog box
Show the names of all of the publishers who are in a certain state.
Display only the name, not the state or the country, because the parameter will be the state.
Sort by the name of the publisher.
[top]
03. I wonder how many university-affiliated publishers we deal with. Let me see a list of all of them, sorted by where they are.
One table, four values, one with a criteria that calls for a wildcard
Show name, city, state, and country fields in the publishers table for those publishers who have the word university as part of their name
- remember, the word university can be anywhere in a name
- make your parameter so that it will return all records that have the word university as part of the name
- you may need to use a wildcard character, and perhaps use it in more than a single location
Sort the results in this order:
- country
- state
- name of the publisher
[top]
04. I was just asked about that guy Prinz or Prince or Prens, but I can't remember how his name is spelled? While you're at it, make the query so that we can use it each time we need to find any authors whose names start with the same letters.
One table, one value, one with a parameter criteria that calls for a wildcard
Use a parameter query to show the names of all of those authors whose last names begin in the same fashion. This query can pull names that begin with any series of letters; they don't have to begin with PR.
Sort by author last name.
[top]
05. We have books in different languages, but how many titles do we have in each language. Let's start by finding out the books for which we have more than two copies, but only the titles of the books that are in German. We need to see the number of copies, the name of the book, and the name of the publisher.
One tables, three values, two with a criteria, and a Boolean (AND/OR) decision to make
Create a list of titles for which we have more than two copies in stock, but only show us those titles that are in German.
This is a touch more complex. You will need a single table for this one, but this time your criteria needs to return values that meet two different criteria
- a more than criteria
- AS WELL AS a specific criteria
Sort the results in this order:
- number of copies, sorted descendingly so that the largest number is first
- name of the book, sorted ascendingly so that when the number of copies is the same, the titles will be in alphabetical order
- name of the publisher
[top]
06. Are we spending too much money on cheap books? We need a list of the titles (just the titles) of all the books whose retail price is less than $10, but only those titles for which we possess more than 1 copy. Sort the titles by the number we have in stock, but make sure the titles are arranged alphabetically.
One table, three values (but only one will show), two with a criteria, and a Boolean (AND/OR) decision to make
You need to query on a criteria that will not be displayed in the result - i.e., we don't need to see the price or the number of books
- show only the names of all of those books that have a retail price below a certain amount AND that have the number of books in stock greater than a certain number
- your resultant table will be sorted first by a field that will not be displayed (books costing less than $10, but only those where the value in the number of copies is greater than 1) and secondarily sorted by a field that will be displayed (the titles of the books that meet the first criteria)
Sort the results in this order:
- number of copies, but do not show this value in the results
- title
[top]
07. Oops, I got that wrong. I meant to say that we want to know the titles of all the books designated as cheap as well as the titles of all the books that we have more than one copy of, whether they were cheap or expensive. This time we need to see both the number and the titles, by number first and title second.
One table, three values (but only two will show), two with a criteria, and a Boolean (AND/OR) decision to make
Show the names of all of those books that have a price below a certain amount OR that have the number of books in stock greater than a certain number. Show the titles and the number of books in stock.
This one is similar to query 06, but here you need to use two criteria with an OR operation. Pay attention to what needs to show in the resultant table.
Sort the results in this order:
- number of copies
- title
[top]
08. My hearing is going and sometimes I can't quite distinguish the difference in the pronunciation of words that begin with B from words that begin with P. Thus, when someone asks about our books, I am not sure I understand which one they are asking for so I need a list of both possibilities. While you're at it, make it so that I can call up books that begin with either of any two letters.
One table, one value, with a two part parameter query and a Boolean (AND/OR) decision to make about the two parameters
Use a multi-part parameter query to do this one.
- single table simple select, but you need to use a two criteria parameter query to make it truly dynamic, so that you can pull results according to the letters you enter as you ask the question
-
show the titles of all of those books whose title begins with either of two letters
(set the parameter so that it will return values for any two letters that are entered into the parameter. Don't restrict it to only B and P.)
Sort the dataset that is returned by title.
[top]