Shelly Cashman: Microsoft Access 2019
Module 2: Querying a Database
1
Project-Querying a Database
• Roadmap
• Create queries in Design view
• Use criteria in queries
• Sort data in queries
• Join tables in queries
• Perform calculations in queries
2
Query
• A query is a question you ask your database regarding the
data contained in that database
• An example of a query is to show all the Account Names and
Account numbers
• A criteria is a condition you can add to your query
• An example of a criteria is to show Account info if the
Amount Paid is less than or equal to 20000.
• Your database will provide answers to your questions
(queries) when you run your queries
3
Creating Queries in the Design View
• Close the tables
• Click CREATE on the ribbon to
display the CREATE tab
• Click the Query Design button to
create a new query
• Click on the desired table name
and click on Add
• Click the Close button to remove the dialog box from the screen
4
Creating Queries – Cont.
• Drag the lower edge of the field list down far enough so all
fields in the table appear
5
Creating Queries – Cont.
• To Add Fields to the Design Grid
• Double-click each field to add to the query
• Click on the Run button to see the result of the query
6
Creating Queries with Criteria
• Click the Criteria row for the field
• Type the criteria
• Click the Run button to run the query
• Click the Save button
• Type the name of the query and click
on OK
7
Clearing the Design area (Design Grid)
• In order to save time when creating multiple
queries, after saving the query you
can Clear 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
• Drag the mouse over the last column heading
to select all the columns
• Press the DELETE key to clear the design grid
• Make sure to save each query using the Save As option so you
don’t overwrite the previous query
8
Creating Queries – Using Comparison
Operators for Dates
• To Use a Comparison Operator in a Criterion
• Open the query in Design view
• Enter the criterion with a comparison operator
9
Sorting
• To Sort Data in a Query
• Open the query in Design view
• Click the Sort row below the field
you wish to sort, and then click the
Sort row arrow to display the sort
orders (Ascending/Descending)
• Click the desired sort order
10
Creating Queries – AND
• To Use a Compound Criterion Involving AND
• Open the query in Design view
• Add the criteria for all the desired fields in the Criteria row
11
Creating Queries – OR
• To Use a Compound Criterion Involving OR
• Open the query in Design view
• Add criterion for one field to the
Criteria row
• Add criterion for another field in
the OR row (the row below the
Criteria row)
• As long as either one of the
conditions are true, the records
will display in the result
12
Creating Queries – No Show
• To Use Criteria for a Field Not Included in the Results
• With the desired query open, click the Show check box to
remove the check mark for a field containing criteria
13
Creating Queries -- Wildcards
• There are two Wildcard
characters: * and ?
• Asterisk (*) replaces any number
of characters
• Question Mark (?) replaces
only one character
• Lets assume you want to search for
words containing two characters
starting with letter M.
- You can use M? as the condition
• If you want to search for words
containing any number of characters starting with letter M.
- You can use M* as the condition
14
Creating Queries – Parameter Query
• To Create a query that asks for the condition as you run the
query, you should create a Parameter Query
• Type a question inside square brackets [ ] under the Criteria
row of the desired field (In this example, City)
• Run the query
• Type the desired condition
(In this example type
Granger)
• You should see the result
15
Removing Duplicates
• To Omit Duplicates
• After adding the desired
field, click an empty field
in the design grid
• Click the Property Sheet
button
• Click the Unique Values property box, and then click the arrow
that appears to produce a list of available choices
• Click Yes and run the query
16
Joining Tables
• To Join Tables
• Click the Query Design button to create a new query
• Add two related tables to the new query
• Add the desired fields from each table to the query
• To Change Join Properties
• Open the query in Design view
• Right-click the join line to produce a shortcut menu
• Click Join Properties on the shortcut menu to display the Join
Properties dialog box
17
Adding Criteria to a Join Query
• To Restrict the Records in a Join
• Open the query containing a join
• Type the criterion for the desired field
18
Calculations – Calculated Fields
• To Use a Calculated Field in a Query
• Add the desired fields
• Right-click the first empty field in the design grid to display a
shortcut menu
• Click Zoom on the shortcut menu to display the Zoom dialog
box
19
Calculations – Cont.
• Type the formula using the
following syntax:
• Title: [Field Name]…..
• Example:
- Tax: [Current Due] *.1
• To format the calculated field,
on the Property Sheet
click on the Format option, and
select Currency
20
Caption
• To Change a Caption
• Open the query in Design view
• Click the field in the design grid to which you wish to add the
caption, and then click the Property Sheet button to display the
properties for the field
• Click the Caption box, and then type the desired caption
• Close the property sheet by clicking the property Sheet button
a second time
21
Functions or Statistics
• To Calculate Statistics
• Click the Totals button to include the Total row in the design
grid
• Add the field for which you
wish to total
• Click the Total arrow to display
the Total list
• Select the desired function
• After running the query, click
the Totals button again to
remove the Total row
22
Functions or Statistics – Cont.
• To group the functions based on a field such as Account
Manager number
-Select the Group By from the Total row
23
