Lesson 7 - Creating simple queries
Finding answers to questions
Now, let's say we've created the Movies table and, using the appropriate Form, we've input the movies that we carry.
Hopefully we have thousands of entries in that table to give our customers the greatest choice possible.
And, if you look at the example below you will note that we usually have several copies of each movie in stock. Each VHS tape or DVD carries a different item number but the rest of the information has simply been copied and pasted from one to the other.
We'll use the Movies table to look at Queries.
A query is a question, a search, an interrogation. In Access it is an object of the database. A query is a stored question. Not the answer, mind you, just the question.
If a customer comes in and wants to know what we have starring Tom Cruise or directed by Spielberg or released in 1995, all those are queries. As long as the information is stored in the database we can get it.
We will also have queries that are repeated all the time and we will have to take care of those also:
"What were the sales figures for last month, for last year?"
"How many customers do we have and what is their average age? Are there more men or women?"
The simplest query would return a list of a whole table. But we rarely need to see all the columns in a table.
The first thing we do when designing a query is to determine what columns or fields we want to see. Usually just enough to describe each record that will display.
"Show me all the directors we have."
I could show just the director's name but that wouldn't be all that useful. I decide that I'll also show the name of the movie and the year it was made.
The example above brings up an interesting problem - if I have 25 copies of a movie by a director, whenever I use a query on director, or star, or title for that matter, the same movie will come up 25 times. What I actually meant was: show me all the unique movies by a director, not the tapes or DVD's.
There is a simple way to correct that and that's to use the Properties of the Query, as shown below:
I can now save the Query so that if the question ever comes up again I only have to go to the Query objects and open the query that I had saved.
I'll give it a name that will remind me of what it does, not just Query1.
Just to remind you again: the query results are not saved here. Only the structure of the query is saved. If we add a bunch of new movies before we run the query again, all those movies, if they apply, will show up next time.
You may have noticed in all the pics above that the title bar always refers to "Select Query". That means that we are selecting something. In fact, we usually select some records from the table. We rarely have to see all the movies, or all the customers.
To select some records we will use the Criteria line in the query to specify a condition for a record to be included in the result.
For example: Show me all Tom Cruise movies in stock. Note: in all these I will use the Unique Values property so that I don't get 4 or 5 repetitions of the same movie.
To define criteria we use comparison operators. These are the signs most people are familiar with from programming, and there are a few new ones.
All movies with a value of $25 or more.
m_Cost >= 25
All movies produced before 1990.
m_YearProduced < "1990"
All movies released after 1995.
m_VideoRelease > #1995-12-31#
Access is very flexible. However you should get into the habit of using punctuation correctly.
Usually, text fields, or strings, are enclosed in quotes: "Tom Cruise", dates are enclosed in number signs: #2000-01-01# and numbers are enclosed in nothing.
The between operator is used like it's pronounced:
m_YearProduced between 1960 and 1970
it is inclusive in that 1960 and 1970 both appear in the result
The like operator is very useful and is used extensively when working with text fields.
It is used in combination with the * , the wildcard character.
Show all stars with a name beginning with Tom.
m_Star like "Tom*"
* stands for "any string", so that the question becomes "Tom followed by something"
Show all movie titles containing the word "wind".
m_Title like "*wind*"
m_Title is "any string, wind, any string"
Of course this will return "Gone with the Wind" along with any title containing "window" or "winding", etc.
Show all movies starring somebody not named Tom.
m_Star not like "tom*"
If there are criteria in 2 columns, both have to be true. This is an AND condition.
What movies have Spielberg and Tom Hanks done together?
To get an OR condition, use the OR line after criteria.
Show movies starring Vivien Leigh or Tom Cruise.
Next week: More complex Queries. See you then!