Filters
Filtering allows
you to ask the database specific questions about information. For example,
you may only want it to show the records in which the Year of Birth
is after the year 1960. You would then be doing a query on the Year
of Birth field. Querying will only show those records that contain
the information you ask it. To do a filter, first create
a query.
Step 1 -- Creating a Query:
| Before you actual perform
a filter, you are going to create a Query where your filter will
be performed later and saved. It's sort of like the Query
in MS Access is a handy little "HOUSE" where your Filter is going
to "LIVE"... |
|
First, create a query by clicking on
the Queries tab under the Objects list and then selecting Create query
by using wizard:
You will be taken into the Query
Wizard which will then let you specify which fields will go into the
Query. First click on the pop-down menu under Tables/Queries
and choose your Table that the Query will use. Then choose
the fields in the left column under that that you want to use in
the Query. Although you can specify only certain fields, you can save time
by just including ALL the fields with the double arrow.
When you click on the double arrow, all the fields on the left will
move to the column on the right. Then you are ready to press
Next.
n
Then choose Open the query to view
information and click on Finish:
You will then see a screen
that looks very much like your Table did in Table View (see
below). But at this point we are going to complete a Filter
in Query View and change the name of your Query to reflect
what is in your filter.
Step 2 -- Creating a Filter
within Query View:
Filtering by Selection
After you are in a new Query
and are now in the actual Query information, you are now going to
do a filter that will bring up only specified criteria. It
literally is like "filtering" out only information that we want, letting
all the other data filter out since we don't want to see that. Let's
say that we want to see ONLY the persons in this sample database that have
Smith as a Last Name. This means that we are
going to do a filter on Last Name to see how many Smith's
there are.
Highlight the information you
want to isolate for the Filter. First, to find all the persons
in the sample database with the name "Smith", highlight the name Smith
in the Query View list. Then go up to Records in the
menu and choose Filter and then Filter By Selection:
You will then have ONLY the
information left that you filtered on -- that is, ONLY the records that
contain "Smith" or whatever you selected will appear in the list
Then choose the X in
the upper right corner of the Query window, say Yes to Save Changes?
Your query will close and go back to the tabbed menu. At this point,
your query is named something generic -- like Table 1Query.
When you select this filter by double-clicking on
it, you will not see the filter applied until you select the filter
button, like this:
Once you press the Apply
Filter button (or you can choose Apply Filter under the Records
menu), your filter will be applied again. Since you can only save
one filter to apply within each query, for every new filter you create
that you want to save, first create a new Query (like you did above) and
create the new filter within the new Query. Then name that Query
something that reflects your particular filter. That way you can
have more than one filter saved, but each one will be under a separate
Query.
Filtering
by Form
There are times when you need to complete
a filter that is not necessarily an exact match (like using greater
than or less than a number). You also might want to look at
more than one data item at a time to filter on. The best choice in such
situations would be Filter by Form.
Filter by Form:
Suppose you need to know how many people had an income OVER a certain
amount, not just equal to a certain amount. This is when you need
to use the Filter by Form feature (not just Filter by Selection).
Example: How many persons had an annual
income of over $17,000?
Set up a place to do your filter (like
in a new Query that you’ve already set up). Then choose Filter and
Filter by Form from the Records menu:
A screen of your fields with empty
data boxes will pop up. You will then find the field you need and type
in the comparison you need:
>17000
| Then press the filter
button: |
 |
You will then see only the following
counties with an annual income of over $17,000:
|
Tips for Using Filter
by Form: |
 |
| Do not use any commas when typing in
a number |
1,000,000 |
Type 1000000
without using commas in the number |
| Use decimal forms of percentages instead
of the percent sign (%) |
50% |
Type .50
using a decimal instead of the percent sign (%) |
| Greater than |
> |
Type a "greater than" sign (>) [using
Shift and the period (.) key] |
| Less than |
< |
Type a "less than" sign (<) [using
Shift and the comma (,) key] |
| Greater than or equal to |
>= |
Type a "greater than" sign (>) and then
an "equal" sign (=) |
| Less than or equal to |
<= |
Type a "less than" sign (<) and then
an "equal" sign (=) |
Using
"AND" & "OR"
Using "AND" Conditions
Sometimes you will need to do a Filter
by Form that includes more than one item. This would be, for example,
if you needed to know all the students who live in Wilmington AND are over
age 10.
Create your Query first. Then
choose Filter by Form. When the window comes up, you would include
all the data you need:
Then press the filter button.
Using "OR" Conditions
Sometimes you may want to know if a
student is either this OR that. For example, you want to know which
students are either age 11 or older OR made over 180 on their Main Test
Score. Then you would use the OR tab at the bottom of the Filter
by Form screen.
Create a new Query and then choose
Filter by Form. Type one condition in:
Then go down to the bottom left and
click on the Or tab:
Copyright 2005 by Dr. Edna H. Mory
