|
Text
 If you
are not in the Query Design Screen, you’ll need to be in that
view. So, go to the Design Screen. Notice that
the third row in the lower half of the
screen indicates Sort: (like the image at the
right). Click-in the Sort: area under Last
Name. A down arrow box appears; click-on the down
triangle. Let's sort the Last Names in Ascending
order. Click-on Ascending. Notice that
Ascending now appears in the Sort: area. Click-on the
( !
) to see the new query. Notice that the names you
entered are alphabetized. Click-on the Design View button
(triangle-ruler-pencil). Now change the Ascending
under Last Name to (not sorted). On your own, try
sorting some of the other fields. When you are finished
remember to set the fields to (not sorted) unless you do
want to sort on those fields.
You may also sort various fields in
your database whenever you are in the Datasheet View,
whether you are viewing the entire
Table, or a Query
from the Table. Notice that the Field Names are shown at
the top of each column in gray cells.
If you click-on one of the gray area
field names (like state), the entire column (Field)
turns “black” (like the image on the right). This
indicates that you have “marked” the entire column
(Field).
In the button bar that appears when
you are editing the Datasheet View, you will see two
buttons with “down” arrows (like the image on the
right). When you move the cursor over these two buttons a
text help box will indicate: Sort Ascending or Sort
Descending. If you click-on one of the buttons, the
Field which you have selected (highlighted) will be sorted
in the order selected. Give this a try and see how it works.
So, there are several “ways” you can sort your
Tables and Queries.
Specific Queries:
So far we have listed everything under
each Field Name that we selected. However, many times you
will probably want to find something specific in
your Table (database - e.g. people from a certain state or
city, people whose favorite number is 7, or salaries between $
20,000 and $ 50,000). This is fairly common sense, but
it can get tricky. To get an idea of various criteria,
you might want to click-on Help in the menu bar. Then,
click-on Microsoft Access Help. The Microsoft
Assistant (normally a paper clip, professor, ball, cat, dog,
or just about anything will appear with a “choice” menu) will
appear with a group of questions “tailored” to the Query screen
you are in. It will look something like the image on the right –
except that your assistant may look different.
Click-on the
first selection: Add or modify criteria.
This will cause the Microsoft Access
Help screen on the left to appear.
Click-on: Learn
about using criteria in queries to retrieve certain records.
When the Microsoft Access Help
screen on the left appears, click-on: Examples of how
to use multiple criteria in the design grid
The next Help screen as seen on the right will
now appear. Follow the instructions and click-on
the little page to go to some examples of query criteria.
The screen below will now appear
and you get some idea of how a specific query
criteria works. Click-on several of the choices
on the left to see what each choice indicates. This
should give you a “feel” on how we’ll do some simple specific
queries.
When you are finished, click-on the “X”
in the upper right corner of the Help Topics menu screen
to close the screen. You may return and explore more of
these help screens as you become more accomplished with Access
database. These help screens are like having a complete Access
2000 manual on your computer.
If you don’t like having the Office
Assistant “hanging around “ on your screen, simply place
the mouse cursor on the Assistant and click
the RIGHT mouse button. This will bring up a menu and
you can choose Hide to have the Assistant “go away.”
Anytime you need the assistant simply click on
Help in the Menu bar and then click-on Microsoft Access Help –
just like you did before.
Now we'll try a few specific queries. First
let's find a specific state.
You should now be back in the Query1: Select
Query menu Design window. It should look like
the one below. If you’re not there click the
Design button in the upper left corner of the screen (like
the one on the right). Click-in the cell
to the right of Criteria: in the State column.
You will see a flashing cursor (Make sure you are in the
State column.). Type-in the abbreviation for one
of the states you entered in your Personnel Table. Your
Query should look like the below picture.
Now click-on
(
!
). A new query window will appear. Only persons from the
state you selected should show. This is a SPECIFIC
query for that state. Click-on Design View Button
(triangle-ruler-pencil) to return to Design View. Now
delete the state you entered.
Now we'll look for Favorite Numbers larger
than 600. Type in >600 in the Criteria cell
under the Favorite Number Column. Click-on (
!
). Everyone with a favorite number larger than 600 should
show. If no one is indicated you don't have a
person with a number larger than 600, or you might have
typed the >600 incorrectly. Return to the Design
View. Delete the >600 and run the query
with no criteria. You should “see” all the
fields again. Return to the Design View again.
Make sure all the Criteria: cells are empty.
Next we'll look for persons with salaries equal to or larger
than $ 20,000 and equal to or less than $ 50,000. In the
Salary field column, in the Criteria: cell type-in:
>= 20000 and <= 50000. Click-on the(
!
). You should now see a specific query that indicates those
persons in the range we chose. Go back to Design View.
Delete the criteria you entered under Salary. Now, on
your own, if you desire, add or delete some fields to your
query and experiment. Don't get frustrated if you no
specific items appear. Frequently you might query for
something that can't exist (e.g. states of VA and
CA – a person can't be from both) or there just isn't anything
that matches. For fun, notice the or: just below
Criteria to the left of the Design View. Try one state in
the Criteria: cell under State and another in the or: cell.
Have fun.
When you have a good feel for queries you’re
ready to end your query session. First click-on File
in the Menu Bar, and then click-on Close. A
Microsoft Access Window will appear and ask: "Do you want to
save changes to the design of query 'Query1'?” Click-on
Yes and a Save As window will appear. Name the
Query anything you like, and click-on OK. When
the Query1: Select Query view closes you will return to the
person: Database screen. Notice that the Query Tab
is active and your new query is available to use again, as you
desire. You can activate this query and change things just like
you did in the tutorial. If you want a printout of your query
(at anytime), simply click-on the Printer Button in the
button bar or on File in the Menu Bar and Print. |