In Lesson 5, when you used the Report Wizard, you chose tblLeads
as the source for the report. This means that all the data in the table
is included in the report. Now you're going to create a query from
within the report.
Open your report in Design view. To create the query, follow these
steps:
From rptLeadCallSheet Design view,
double-click the Select Report button,
which is the small gray button in the top-left corner of the report
directly under the View button.
This opens the Properties window for the Form.
Click the Data tab. The Record Source should be tblLeads.
Click tblLeads, and then click the Build
button to the right (looks like 3 dots). A warning will appear
asking if you're sure, as shown in Figure 5-1. Click Yes.
Figure 6-1: New Form, Design View.
This opens a Query Design window (discussed in the following
section).
Choose File > Save, and name the
Query qryLeadCalls.
When creating a query from within a report, it's not necessary to
save your query. It's kept within the report. In some cases, however,
you'll want to save your query as you just did so that you can use it in
more than one place. In most cases within a report, you can simply use
the Build button as you just did to create a query and when you leave,
it asks whether you want to save it -- just as it will in this example
-- but it will save within the report.
Query design view
In the Query Design window, be sure your viewing the query in Design
view as shown in Figure 6-2. In the top half of the Query window is your
tblLeads table with the list of fields. The
bottom half of the window contains the design grid. Only fields placed
in the design grid are shown in the query results.
Figure 6-2: Select Query Design View.
To get the fields you need in your report down to the design grid,
simply double-click each one in the table above the design grid. Scroll
down in tblLeads to double-click ContactFirstName,ContactLastName,Title,
CompanyName, Phone, and StateID.
As you double-click each field, it moves to the bottom of the query
window to the design grid. Choose File > Save
as you go along.
At this point, nothing has been changed for your report. To limit
records, you need to enter something in the Criteria row of the design
grid under the field you want to limit.
In this case, you want to click under StateID and enter a state
abbreviation that's in your database. In this example, AZ is chosen. By
entering a state ID here, you'll only see records that match this state.
Toolbar
Once again, a unique toolbar is shown, but there are many similar
items, as well as a few strange new buttons. The first toolbar button,
as always, is the one that lets you switch views. For a Query, this is
Design view and the normal view is a Datasheet view, which looks very
much like a table and in fact, the data you see is directly connected to
the table, so don't delete or change anything unless it's a permanent
change. A new view for a query is the SQL view.
SQL stands for Structured Query Language and a form of it is used in all
databases. Select SQL View to see the code
that pulls the data from the table. It should looks something like this:
SELECT tblLeads.ContactFirstName,
tblLeads.ContactLastName, tblLeads.Title,
tblLeads.CompanyName, tblLeads.Phone, tblLeads.StateID
FROM tblLeads
WHERE (((tblLeads.StateID)="AZ"));
This shows the fields selected, as well as table name and the limit
put on the data.
You do not need to learn SQL in Microsoft Access, but if
you ever desire to learn it, you can examine the code in this view after
creating the query in the Design view.
The next few buttons are standard but there's a new one you need to
become familiar with. It's the Query Type button with a dropdown menu
(to the right of the Undo button). This button allows you to choose the
type of query you want to select, as shown in Figure 6-3.
Figure 6-3: Special Query Toolbar
buttons.
The following six types of queries available are as follows:
Select Query: The default query selects
records based on criteria, but can also have calculations built in
as well as pull data from multiple tables.
Crosstab Query: Compares and compiles
data, similar to a spreadsheet.
WARNING
The next four are all classified as Action Queries and have an
exclamation point next to them to identify this. Be careful with these
because they change data when they run -- unlike the Select or Crosstab
Queries.
Make-Table Query: Creates a new table
based on data pulled from other tables.
Update Query: Can update one or many
fields, such as making a status field inactive for records whose
lead date was earlier than a certain date.
Append Query: Adds records from one
table to another.
Delete Query: Deletes records from a
table based on some criteria (or deletes all).
Here are the other buttons available on the toolbar:
Run: Applies your criteria to the table
and shows the results in a datasheet. Use this button rather than
the Datasheet view to have the most updated view.
Show Table: Shows a list of all tables,
allowing you to add another table. If you double-click your tblStates,
it appears in your Design view and a line connecting the tables
indicates a link between them. This is the Lookup created in an
earlier lesson.
Totals: Toggles a totals row in the
design grid. This row allows groupings similar to the Sorting and
Grouping window in reports. You can use queries or reports to
control sorting and grouping. Be sure to check your data and not
conflict.
Top Values: Allows you to limit large
tables to just the top number or percentage of values.
Properties allows you to set query
properties.
In the design grid at the bottom, each column represents a field. The
rows are labeled on the left side, as follows:
Field is normally a field name, although
code can be written, for example:
This code would create a field named Contact and would concatenate
(put together) the two real fields with a space between them. Remember
to include the space. Anything put between quotation marks is taken as
literal.
Table shows where the result comes from.
For instance, because you added your table, tblState, you can use
either table to pull information from. Notice the tab for queries.
This indicates that you can pull information from tables or other
queries.
Total can be toggled on or off. It's
used mainly when there's a field to be totaled or summarized.
Otherwise, it's best to use the groupings in the report.
Sort allows the records to be sorted by
any fields -- ascending or descending.
Show is a checkbox that can be toggled
on or off. Perhaps you want to limit data according to the StateID
field, but you don't actually want to show that data. You could
select not to show it.
The remaining rows are all criteria
related. In Figure 6-4, you see a sample of StateID limited by AZ and
the CompanyName limited by Like A*, which
means, starts with A.
Figure 6-4: Select Query Design View.
Because the two criteria are on the same line, a record has to be
both in Arizona and start with an A. If either criterion were moved to a
line below, a record would be included in the results list if it was
either in Arizona or started with an A.
TIP
Once again, a reminder to check your data to be sure that what you think
you're asking for is indeed what you're getting.
Select Queries are just the tip of the cake. There are many more
powerful things you can do with queries and reports, and even more so
with the other types of queries. Now that we've reached the end of
queries in this introduction course, we'll move on and briefly cover
macros and code.
Macros and code
One of the most powerful abilities of Access is the VBA coding behind
the scenes.
TIP
VBA is very closely related to Visual Basic programming. A course in one
will help with the other, but be sure to check specific syntax.
Coding can be used to automate forms, reports, and other objects in
Access. A macro is a set of directions.
Although Access uses macros, VBA code is the preferred customization
method. It's ultimately more flexible and powerful, plus all code is
contained on a related module within the object, whereas Macros are
contained elsewhere in the database.
Learning VB or VBA has even further reaching benefits. For example,
ASP is one of the most commonly used data access pages on the Internet,
and it's related to VB. Here's a bit of ASP code.
IF (rsCustomer("DQWin")=true) then 'PERSON WON BEFORE
response.write "Sorry, you won on " &
formatdatetime(rsCustomer("DQDate"),vblongdate)
& "."
Visual Basic has long been the basis for beginners to learn how to
program. Much of the difficulty lies in the logic rather than the
language. It is, however, a large step and often it's just enough to
know it is possible.
Review
Now that you've seen a fast view of Access, be sure to go back
through the course and review areas you may have missed or need to
examine more closely. It's often hard to grasp a new software program in
the first learning process. With Access, there's always more to learn,
even after years of working with it. If you look, you'll find a wide
range of experts whose knowledge may only overlap in areas.
Here's a quick review of the parts of Access you've covered in this
course:
Tables: All information is stored here.
Queries: A tool to sort, modify, update,
delete, or otherwise manipulate data. Data modified here is also
modified in the tables. There are six basic types of queries:
Select, Crosstab, Make-Table, Update, Append, and Delete. All of
these are Action Queries except for Select and Crosstab. Be cautious
in their use.
Form: A face on the data, but again,
data modified here is also modified in the tables.
Report: A method to create printable
lists of data in formats that are easy to read and understand. Any
number of reports can be created for specific needs.
Future learning may include Pages, Macros, Modules, and Class
Modules.
Moving on
In this lesson, you created an associated query to your report and
then modified it to your needs and preferences. During this process, you
looked at queries in design and result view. Continue to build queries
as needed to add functionality to your database.
Remember that Access grows with your needs. In most small businesses,
Access will have more capabilities than ever get used. In businesses
that grow beyond Access' capabilities, data is always portable to
another format.
Using either a direct upgrade to SQL Server or exporting into CSV
(comma-separated value) format, you can import your data into any other
database.
Follow up with the final assignment and quiz, and then get creating
on your own database. Practice makes perfect and the sky's the limit!
Assignment
Create a new query -- this time from your database window instead of
through the report. Choose the tblLeads and
make a query to list the name and the address information. Concatenate
the Name and the City, and the State and the Zip (call it the CSZ
field). Sort by State, and then City. Your results should include the
information in the following table.
Name
Address
CSZ
Cindy Fox
123 Main St.
Mesa, AZ 85204
Jimmy Jones
234 S. Main St.
Baltimore, MD 21204
David Gardner
517 E. 12th St.
Glen Burnie, MD 21061
Quiz
Question 1 True or False: Queries can be saved as individual items that show
up on the query tab in the database window or as a SQL statement that
is only accessible through the report.
A)
True B)
False
Question 2 True or False: Microsoft Access is good for simple uses, but it
cannot grow with your data.
A)
True B)
False
Question 3 True or False: When editing data in a field, you never have to
worry about using the Save button to make sure the change is recorded.
A)
True B)
False
Question 4 True or False: If you delete records in a Query, you're not
affecting the records in the table.
A)
True B)
False
Question 5 True or False: Automatically generated code is more efficient than
manually generated code.
