James's Database
~~James
La Borde
Queries
The Backbone of a Great Database
If
you have been following our continuing saga of database development, you
should have a database built, populated with tables and even a form or
two to get data into your database. “Now What?” you are probably
asking. Queries are the tools used to analyze, maintain, update, delete,
and restrict data. Queries are the tools that make Microsoft Access the
better choice (over Microsoft Excel) for repetitive data storage and
analysis.
Topics
covered in this Article:
The Advantages of Using Queries over an
Excel Spreadsheet
Many
users of Excel never realize the power of an Access database with the
appropriately applied Queries. Many a spreadsheet has been used where a
report based on a query could have done the job much more efficiently.
There are some major advantages and others that are becoming less
critical. Let’s take a look at several of them.
The
biggest advantage that using queries based on a table has over a
spreadsheet is flexibility. The user is able to manipulate the data much
more easily. Let’s take a look at a real life example. I used to work
for an amusement park. The Rides department kept track of all of their
data in Excel spreadsheets, one for each and every day. This sounds
reasonable to your average Excel user. However, what happens when the
boss now wants a report based on weekly totals? You now have to create a
new spreadsheet pulling data from all of the appropriate spreadsheets.
Then the boss asks for a monthly report. More work for the user, either
compiling the data by hand or programming. Now, if they were using (they
are now!) an Access database with the appropriate tables and queries, it
would be a simple matter of changing the criteria for one field to
gather data for a specified time frame. We will be discussing criteria a
little later, but it is a much simpler way of compiling the data you
need than adding numerous spreadsheets.
Another
major advantage of using an Access query over a spreadsheet is the fact
that formulas are entered only once. Any changes to your formulas are
likewise only entered once. This means no copying and pasting or using
the Fill Down option on a formula. This is actually a two-part
advantage! First, it makes modifications to the formula easier. Second
is file size (although memory and hard drives are becoming less
expensive). An Excel spreadsheet needs to store your formula every time
you use it. In an Access query, the formula is saved once and applied as
needed for every row that shows up in your query. This doesn’t sound
like much, but when you have several formulas in a row and several
hundred or even thousand rows, it can add up. I have personally tested
this and the Access query does use significantly fewer resources as the
amount of data increases.
<<<back
to top
Types of
Queries
The Select Query
The
Select query is the most commonly used type of query. It is used as a
source for Forms and Reports as well as to preview the other types of
queries. The Select query does just as the name suggests, it allows the
user to select the data that they choose to see. This gives the database
designer a lot of flexibility. It is what enables the designer to
further limit the data that the end user may see. Not only can it limit
what data is seen; it can allow the user to see data from more than one
table. Let’s look at an example, a database for a distribution center.
The
database has three users: a supervisor, a data entry clerk, and a
shipping clerk (all referred to here as "he" for ease of
reading only). The designer wishes to allow the data entry person to
enter all of the order info, but not see anything about the shipping
information and order fulfillment. The shipping clerk only needs to see
the items ordered, not the credit card information or order history. The
supervisor, on the other hand, needs to see all of the data. The
designer builds the form for the data entry employee from a Select query
that displays only order information. The shipping form is based on a
Select query that only pulls the items ordered from the data entry form
and displays it to him. The shipping clerk then fills in his data (for
instance, location or out of stock notation) as he fills the order.
Finally, the supervisor gets to see a report, based on a different
Select query. This Select query retrieves all of the information from
both the data entry and shipping forms, along with other information
from a product table so that the supervisor can see a very detailed view
of what’s been happening. The Select query is also dynamic as far as
time periods; the supervisor can enter a date or date range to get a
wide or narrow view.
In
our example, we have used the Select query in a number of ways. First,
we used it as the basis for a Form that allows the data entry clerk to
input only a portion of the data. Next, it is used as the basis for a
form that allows the shipping clerk to view a portion of the already
entered data and to add additional information that the first user never
even saw. Then we passed it on to a supervisor and used the Select query
as the basis of a Report. This select query not only selected specific
data for the report; it also pulled additional information from a second
table to make the report more meaningful. Not only does it pull data
from two tables, but also using the same query and report, allows the
supervisor to change the report to reflect his current needs.
<<<back
to top
The Append Query
The
Append query pulls data from one source and adds it to an existing
table. One use for this can be to keep the current data in a temporary
table and then move it into an archive table once it’s no longer
needed. It is also the best way to remove duplicate data from an
existing table! To remove duplicate values, go to the Tables tab and
copy the table that may have duplicates and paste it in the same area.
When given the option, select Structure Only (see Figure 1). This will
copy the structure of your table but not the data. Go into design mode
of your newly created table and make the field(s) with duplicates you
wish to remove your Primary Key(s). Next, create an Append query. Use
your original table as the source and your new table as the destination.
When you Bang ( )
the query, a pop-up will appear telling you that it is unable to append
X number of records due to Key Violations, and do you wish to Continue?
As you may remember, no duplicates are allowed in Primary Keys, and
that’s why the message about Key Violations pops up. If you answer
yes, all but the duplicates will be appended. You now have a new table
with no duplicates.
<<<back
to top
The Update Query
The
Update query enables the user to change large numbers of records at the
same time. This type of query is powerful but does have its limitations.
It can update one or more fields in a table on any number of records. The
biggest limitation is that it is generally updated to a specific value,
whether that’s a static value or the value of another field in another
table. Let’s look at a couple of real world examples.
First,
a record retention database holds all of the data on all of the records a
company keeps. While retaining data is important, not all of it needs to
be kept indefinitely. The Record Retention Specialist has 123 boxes of
files destroyed as their retention period has expired. Rather than having
to go in and update each record manually, the designer gave them the
option of listing all of the boxes and running an update query to set the
destruction date and status on all of them at the same time. Another more
personal example would be with your Bank Account. Your bank pays you
dividends (or charges you fees) based on either your Lowest Monthly
Balance or your balance at month-end. If they base it on your month-end
balance, they would run an Update query to update your month-end balance
with your balance at the end of business on the last day of the month.
<<<back
to top
The Delete Query
The
Delete query is another obviously named query. It does just what it says
it does, it deletes data. This can be a very powerful, and very dangerous,
tool. This allows the user to delete all data matching the criteria given.
A word of caution: run a select query with the same criteria you are
planning on for your delete query before running your Delete. This will
allow you to see what is going to be deleted before it is actually
deleted. Remember, unlike in other Office programs, changes in Access are
done immediately. Once you run the Delete query, the items are deleted;
you do not have the option of reverting to a saved file if there was a
mistake made!
Access
does not require the user to hit save to make changes to the file. A
change or newly added information is saved as soon as the row (record) it
is in is left. A delete query makes this change immediate as it goes in,
makes the deletion, and moves on to the next row. For the most part, this
“automatic save” is advantageous, but it can be disastrous as well.
<<<back
to top
The Make-Table Query
A
Make-Table query allows you to pull a subset of data out of your database
and make a new table out of it. One example would be for your marketing
department. Let’s say that they want to start a campaign in one region
of the country. You wouldn’t want to give them all of your customer
data, so you run a Make Table query to pull out just the names and
addresses of the companies for the region they are servicing. You export
that data for them, they are very happy with you, and your other customers
are happy because their data is not floating around out there and they are
not getting sales literature on something that does not apply to them.
This type of query can also be used to archive old data.
<<<back
to top
More Advanced Query Methods
– Union, Cross-tab and Aggregate Queries
We
have reviewed the major types of queries that will be used most often.
There are other types of queries that are a little more advanced and less
commonly used. The first of these is the Union query. Access thinks
that this type of query is so advanced that it is not a default option.
The only way to create a Union query in Access is to type it in, in the
SQL view (To get here, click View, SQL View). A Union query allows you to
combine two queries into one. There are, of course, strict limitations.
Both queries must have the same number of columns and each column must
have the same data type. I will show you an example of a Union query at
the end of this article.
Cross-tab
queries are one of the few queries that are not obviously named. If
you are familiar with Excel, a cross-tab query is similar to a Pivot
Table. It takes values from rows in your table and creates columns out
from those values and allows you to group data in this manner. If you wish
to try one of these queries, I highly recommend using the Wizard to walk
through it numerous times before you ever try doing one on your own.
Aggregate
queries are somewhat obvious. They allow you to group your records in
a variety of ways. There are several options for each column and you can
combine any number that you like. The available options for each column
are Group By, Sum, Avg, Min, Max, Count, StDev, Var, First, Last,
Expression and Where. Not only can you include any column in this type of
query; you can include it more than once. One limitation is that a column
listed as where can not be included in the result.
<<<back
to top
How Do I Select My Query Type?
First,
remember, it is a recommended practice to design queries of all types in
the default Select mode first, then once the appropriate data is selected,
change your query type. The icon to change your query type is ( )
and to change your select query into an aggregate query ( ).
The query type icon will pull down a selection of query types for you. The
Aggregate query is actually a function as it is truly a collection of
functions acting together in a single query.
<<<back
to top
Designing a Query
Creating
your own query is relatively easy. First on the Database window (Figure 2)
select the Queries tab. Then select New. This will bring up an option box
with the following options: Design View, Simple Query Wizard, Crosstab
Query Wizard, Find Duplicates Query Wizard, and Find Unmatched Query
Wizard. The simple query Wizard is very basic, so this is one you can try
on your own. Design View will open up your query window and an open table
option on top of it. Select the table(s) you want to base your query on,
then click add, then close. You are now at your basic query window. Take a
moment or two to acquaint yourself with the layout. To select fields to
include in your query double-click them in the table window. Now that you
have your fields in the query, click the Datasheet icon ( )
in the upper left-hand corner. This will preview what the query will
select. You should see all your selected columns in the datasheet window.
You can opt to sort in either ascending or descending order on one or more
fields. Remember Access works left to right here so the first field it
finds a sort order on will be done first with subsequent fields being
sorted within that sort. To filter our extra data, we will apply criteria,
which is covered in the next section. In the meantime, let’s click the
disk icon and save our query as it currently is.
<<<back
to top
Criteria
What Are Criteria and How Do
I Use Them?
Criteria
are a set of rules that the designer applies to a query to filter the
data. In SQL it is called the Where Clause. Criteria can be left blank to
select all the records in the table or can be refined down to a point that
only a single record is selected. Multiple criteria can be selected for
each column. The key thing to remember is that all criteria on a single
line in the Criteria section must be in the record for it to be included
in the resulting subset of data. If you would like to include a record
with this OR that, you must use more than one line in the Criteria section
(Figure 3). This allows you to use more than one set of criteria in one
query. As you can see in the figure, a set of criteria has been entered
for the column status. If I wanted to select all of the records that meet
these criteria and were over a specific amount, I would enter the amount
on the same line as the existing criteria. If I wanted to match these
criteria or be over a specific amount, I would enter the new criteria on
the next line down.
<<<back
to top
Using Criteria to Make a Dynamic
Query
When
a user needs to run relatively the same query repeatedly, you may want to
create a dynamic query for them. For instance, a supervisor wants to see
the out of balance reports for an employee for an indeterminate time
frame. First, to create dynamic criteria we enclose a request in brackets
in the criteria field. Something like ‘[Please input the employee number
for this report]’ in the criteria section of the employee field. When
this query is run, it will ask the user, using your phrase, to input the
criteria they desire for this report. In our example, we would also enter,
‘Between [Please enter Beginning date for report] and [Please enter
ending date for this report]’ under the date field. This would allow the
supervisor to run a report listing all out of balances for any employee
for any time frame. These requests can also be tied to a specific form if
you wish. Dynamic criteria can save you an immense amount of time in
developing custom queries to meet all of the reporting needs of your user.
<<<back
to top
Automating Dynamic Queries
Many
times, a report will need to be dynamic but it will get tedious for the
user to input data again and again. In these instances we can use Built-in
Access functions as criteria. Looking at the above query, let’s assume
that the supervisor does not need a variety of dates but is only
interested in last month’s data only. Rather than requiring the user to
input those dates repeatedly we can adjust our criteria to match. For
dates, I find the following criteria to get last month’s dates only
useful. First, create two new columns. In the first column we will create
a portion of the date. Enter the following in the Field under the new
column: EffMonth: Datepart(“mm”, [Effectivedate]). Next in the
criteria field for this column enter: DatePart(“mm”,Date()-Datepart(“dd”,Date()).
I know this looks rather complicated but you are using two of Access’s
built in functions to determine the month in the date field and last
month. In the next column, enter ‘EffYear: Datepart(“yyyy”, [EffectiveDate]
). Again, we are using an Access built in function to pull the year out of
the EffectiveDate field. For the criteria on this column enter, ‘DatePart(“yyyy”,Date()-Datepart(“dd”,Date())’.
You can uncheck the Show box for these two columns as they are strictly
filters. Now run the query and you will see that you only have last
month’s data. There are many other functions available to use in
filtering your data in the criteria. If you are interested in other fun
functions in Access please let me know. I will be more than happy to fill
you in on some and I will probably writing an Article on them in the near
future. |
,
