Transcript Microsoft Access 2010

Microsoft
Access 2010
Chapter 2
Querying a Database
Objectives
•
•
•
•
•
•
•
Create queries using Design view
Include fields in the design grid
Use text and numeric data in criteria
Save a query and use the saved query
Create and use parameter queries
Use compound criteria in queries
Sort data in queries
Querying a Database
2
Objectives
•
•
•
•
Join tables in queries
Create a report and a form from a query
Export data from a query to another application
Perform calculations and calculate statistics in
queries
• Create crosstab queries
• Customize the Navigation Pane
Querying a Database
3
Create folder for this chapter
• Create a Ch. 2 folder under your Access folder in
your home directory
• Copy the Camashaly Design database from your
chapter 1 folder to your chapter 2 folder to use it
for our new projects.
Querying a Database
4
Project – Querying a Database
Read Project –
Querying a
database on pg.
74
Querying a Database
5
General Project Guidelines – for Queries
• Identify the fields – which fields from which tables?
• Identify restrictions – does the query include ALL records or a
restricted group?
• Determine whether special order is required – should you sort
the data?
• Determine whether more than one table is required – special
items to take care of if items are from more than one table
• Determine whether calculations are required – Group totals,
individual totals, etc.
• If data is to be summarized, determine whether a crosstab
query would be appropriate – if data is to be grouped by 2
different types of information, a crosstab query will work great!
Querying a Database
6
Creating a Query in Design View
• Open the Camashaly Design database in your chapter
2 folder.
• Previously we used the query wizard, now we will
create queries in Design view.
• Click on the Create Ribbon, then on the Query Design
button.
• Add the Client Table (double click, or click then click
add button)
• Close the Show Table dialog box.
• Resize the box so you can see all the fields
Querying a Database
7
Creating a Query in Design View
Querying a Database
8
Adding Fields to the Design Grid
• Do page 80-82 You can use the View button or the
Run button to see the results of a query.
Querying a Database
9
Using Saved Queries
• Easily change the layout to modify it for future
queries
• Can be easily printed
Querying a Database
10
Using a Wildcard
• Wildcard – a symbol that represents any character
or combination of characters
– * - represents any collection of characters
– ? – represents any individual character
• Do page 83-86.. Call me over and let me see it.
Querying a Database
11
Using a Wildcard
Querying a Database
12
Parameter Query
• Sometimes we need to allow the user to enter
information which needs to be searched for.
Querying a Database
13
Creating and Viewing a Parameter Query
Build a parameter query.. Pg. 87-91
Querying a Database
14
Using a Compound Criterion
Involving AND
• AND – both criterion must be true (same row)
• OR – Either criterion must be true (different rows)
Querying a Database
15
Using a Compound Criterion
Involving OR
• Do pages 91-93
Querying a Database
16
Special Criteria – pg. 94
• Range of values for a single field – see example 1
• BETWEEN operator – example BETWEEN 1000
and 4000
• IN operator – IN followed by a list of values in
parenthesis. For example IN (“11”, “14”)
Querying a Database
17
Sort Order
• What is the sort key?
• Is there more than one sort key? (Major and
Minor) Major sort field MUST appear to the left
of the minor sort field in a query!
• Are there any restrictions? One common
restriction is to exclude duplicates. Another is to
list only a certain # of records
Querying a Database
18
Clearing the Design Grid
• Open the query in Design view
• Click just above the column heading in the first
column in the grid to select the column
• Hold the SHIFT key down and click just above the
last column heading to select all the columns
• Press the DELETE key to clear the design grid
• Now do pages 96-99
Querying a Database
19
Creating a Top-Values Query
• This allows you to show only a specified number
of records (top 5, top 10, etc.)
• Do pages 96-100
Querying a Database
20
Joining Tables – do you need more than
one tables’ information?
Finding records in
two table that have
matching values.
See page 101
Querying a Database
21
Joining Tables
• Click the Query Design button (Create tab |
Queries group) to create a new query
• Add two related tables to the new query
• Add the desired fields from each table to the
query
• Next slide
Querying a Database
22
Joining Tables
For the JOIN to
happen properly…
the fields have to
be spelled EXACTLY
alike, be the same
data type, width,
etc.
If you still don’t
have a join line..
.create one
yourself… I will
show you how.
Querying a Database
23
Joining Tables
Do pages
102-108
DO NOT
PRINT THE
REPORT!
Querying a Database
24
Creating a Form for a Query
• DO pages 109-110
Querying a Database
25
Exporting Data to Excel
• There are many times that others may need the
data you have in your tables in some other
format…either Word or Excel
• We have already imported from Excel, now let’s
export to excel.
• There are steps here also to create a .rtf file or a
regular text file.
• Do pages 111-113
Querying a Database
26
Restricting the Records in a Join
• Do page 115
Querying a Database
27
Using a Calculated Field in a Query
• Is there something that you need to calculate on
the fly? If so,
• Choose a name for the calculated field
• Determine the format for the calculated field
(currency, # decimal places, etc.)
Querying a Database
28
Using a Calculated Field in a Query
Do pages
116-119
Querying a Database
29
Using Criteria in Calculating Statistics
• There are some built-in statistics such as COUNT,
SUM, MIN, MAX, STDEV, VAR, etc.
• These are called aggregate functions – they
perform mathematical functions against a group
of records.
• Do pages 119-122
Querying a Database
30
Using Grouping
Grouping – creating
groups of records that
share some common
characteristic.
Do page 122-123
Querying a Database
31
Crosstab Queries
Crosstab queries calculate a statistic (sum, avg, count) for data that is
grouped by 2 different types of information. One group will be horizontal
and the other vertical (rows and columns)
Do pages. 124-128
Querying a Database
32
What now?
• Homework
• Learn it Online –
• Labs
– In the Lab, Lab 3 – Philamar
Training DB – pg. 135
– Cases & places Case 1 –
Chamber of Commerce –
pg. 135-136
– Pg. 129- Who wants to be a
Computer Genius?
– Make sure you choose
Chapter 2
• BEFORE you can do the
– www.scsite.com/ac2010
labs for this chapter… you
– You can do a screen capture
better make REALLY sure
or print it.. But you get
what you get, got it?
that your tables are
corrected from ch. 1
• Test - TBD
Querying a Database
33