Download - Shelly Cashman: Microsoft Access 2019
1
Shelly Cashman: Microsoft Access 2019
Module 2: Querying a Database
-1-
Objectives (1 of 2)
• 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
-2-
Objectives (2 of 2)
• 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
-3-
Introduction
• A database system stores data and has the ability to find answers to questions related to the data
• Query is a question you pose to Access or any other database management system
• To find the answer to a question, we first create a query using techniques illustrated in this module
• Then you instruct Access to run the query
• Access performs the necessary steps to obtain the answer and display it in Datasheet view
-4-
Project—Querying a Database (1 of 4)
-5-
Tables in CMF Vets database:
Project—Querying a Database (2 of 4)
-6-
Project—Querying a Database (3 of 4)
-7-
Project—Querying a Database (4 of 4)
-8-
Creating Queries (1 of 18)
•To Add Records to the Database• (Optionally we can import the tables into CMF Vets) • Open the database (CMF Vets)• Open the tables (Owners, Patients, Veterinarians) in
Datasheet view, then close the Navigation Pane• Click an open cell below the last record • Enter the data for Owners
-9-
Creating Queries (2 of 18)
•To Add Records to the Database (Continued)
• Enter the data for Patients
-10-
Creating Queries (3 of 18)
•To Add Records to the Database (Continued)
• Enter the data for Veterinarians
• Close the tables (Owners, Patients, Veterinarians)
-11-
Creating Queries (4 of 18)
•To Create a Query in Design View
• Query Design method has more options for creating queries
• Click the “Shutter Bar Open/Close Button” to close the Navigation Pane
• Click Create on the ribbon to display the Create tab
• Click Query Design button (Queries grp) to create a new query
• Click the table (Appointments) to add to the query
• Click the Add button to add the selected table to the query
• Click the Close button to remove the dialog box from the screen
• Drag the lower edge of the field list down far enough so all fields in the table appear
-12-
Creating Queries (5 of 18)
• To Add Fields to the Design Grid
• Double-click each field (from Veterinarian to Owner: 6 fields)
• Only fields added are included in the query results
-13-
Creating Queries (6 of 18)
-14-
Creating Queries (7 of 18)
• When using queries we usually look for records that satisfy some criterion (e.g. show information for Dr. Gomez)
• Without a criterion or criteria, Access will show all records
• To Use Text Data in a Criterion• Click the Criteria row for the field (Veterinarian) to produce an
insertion point
• Type the criterion (G01)
• Click the Run button (Design tab | Results grp) to run the query
• Click the Save button on the Quick Access Toolbar to display the Save As dialog box
• Type the name of the query (“m02q01”)
• Click the OK button (Save As dialog box) to save the query
-15-
Creating Queries (8 of 18)
• Wildcards are symbols that represent any character or combination of characters
• Asterisk (*) – any collection of characters
• Question mark (?) – any individual character
• To Use a Wildcard• The following steps modify an existing query to use * wildcard to find
out how many owners live in Utah.
• Open the Query (Owners) in Design view
• If necessary, click the Criteria row below the desired field (Owner State) to produce an insertion point
• If necessary, delete the current entry
• Type the criterion containing the wildcard character (U*)
• Click Run button (Design tab | Results grp) to run the query
• Click File->Save As->Save As Object->Save Object As->Save As (m02q02)-16-
Creating Queries (9 of 18)
• To Use Criteria for a Field Not Included in the Results• With the desired query (Owners Query) open, click the Show
check box to remove the check mark for a field (Owners Postal Code) containing criteria
• Type the criterion (813*) and run the query
-17-
Creating Queries (10 of 18)
• A parameter query allows us to specify the criterion at run time so we don’t need to create a new query or change an existing query
• To Create and View a Parameter Query• Open the query (Owners) in Design view
• Click in the Criteria cell for a field (Owner State) and enter a parameter query using brackets ([Enter a State], for example)
• Run the query
• Save the query
File -> Save As
-> Save Object As
-> Save Object As
-> Save As
(Owner-State Query)
-> OK -18-
Creating Queries (11 of 18)
• To Use a Parameter Query• Open the Navigation Pane
• Right-click on the query (Owner-State Query) to produce a shortcut menu
• Click Open on the shortcut menu and display the Enter Parameter Value dialog box
• Type desired information
• Click OK
• Close the query
-19-
Creating Queries (12 of 18)
• To Use a Number in a Criterion• Close the navigation Pane
• Click Create on the ribbon to display Create tab
• Click the Query Design button (Queries group) to create a new query
• Click the table (Treatment Cost) you wish to add to the query
• Click the Add button to add the selected table to the query
• Click the Close button to remove the dialog box from the screen
• Add the desired fields (Treatment Number, Treatment, Animal Type, Cost) to the query
• Add a numeric criterion (25) for a numeric field (Cost)
• Run the query
• Save the query as m02q03
• Close the query -20-
Creating Queries (13 of 18)
• If you want a query to return something other than an exact match, you must use an appropriate comparison operator
• To Use a Comparison Operator in a Criterion• Create a query using Query Design [Create tab | Queries grp]
• Add the table (Appointments)
• Include the fields (Appointment Date, Appointment Time, Treatment Number, Veterinarian, Patient ID)
• Enter the criterion with a comparison operator (>6/30/2021) for the field (Appointment Date)
• Run the query
• Save the query as m02q04
• Close the query
-21-
Creating Queries (14 of 18)
Figure 2-25: display of a comparison operator-22-
Creating Queries (15 of 18)
• When searching date using more than one criterion, we call it a compound criterion
• AND / OR are used to create a compound criterion
• To Use a Compound Criterion Involving AND
• Starts a new query [Create tab | Queries grp | Query Design] using the Appointments table
• Include the fields (Appointment Date, Appointment Time, Treatment Number, Veterinarian, Owner)
• Add the criteria for two fields in the same row (Criteria row) (Appointment Date = “>06/30/2021”, Veterinarian = “B01”)
• Run the query
• Save the query as m02q05
-23-
Creating Queries (16 of 18)
• Figure 2-27: display of AND criteria on the same row-24-
Creating Queries (17 of 18)
• To Use a Compound Criterion Involving OR
• Open the query (m02q05) in Design view [Home tab | Views grp | View]
• Add criterion (>6/30/2021) for one field (Appointment Date) to the Criteria row
• Add criterion (B01) for another field (Veterinarian) in the or row (the row below the Criteria row)
• Run the query
• Save the query as m02q06
-25-
Creating Queries (18 of 18)
• If you wan to create a criterion involving a range of values in a single field, use the following three operators
• Special Criteria• AND operator
-E.g. Cost: >=20 AND =35
• BETWEEN operator-E.g. Date: BETWEEN 1/10/2021 AND 10/25/2021
• IN operator-E.g. State: IN (‘CO’, ‘UT’)
-26-
Sorting (1 of 6)
• Instead of creating a new query for a table from scratch, you can also do it by clearing the grids of an existing query for that table
• To Clear the Design Grid
• Open the query (m02q06) in Design view
• Click just above the column heading in the first column (Appointment Date) in the grid to select the column
• Hold the SHIFT key down and click just above the last column heading (Owner) to select all the columns
• Press the DELETE key to clear the design grid
• You can create a new query if needed
• Close the query without saving the changes
-27-
Sorting (2 of 6)
• To Import a Table• Open the desired database (Support_AC_CMF_Vets Extra Tables)
and then click the External Data tab. Both databases are now open in Access
• In the database you want to import to (CMF Vets), Click the “New Data Source” button [Import & Link group]
• Click From Database in the New Data Source Menu, and then click Access to display a dialog box
• Click the Browse button and navigate to your storage location for the file (Support_AC_CMF_Vets Extra Tables) and click Open button
• If necessary, click the “Import tables, queries, forms, reports, macros, and modules into the current database” option button
• Click OK, to display Import Objects dialog box and select the table to import (Veterinarian Vendors), and click OK
• Close the dialog box without saving the import steps• Confirm the table is a table object in the Navigation Pane
-28-
Sorting (3 of 6)
• To sort the query results you need to specify the sort key
• The following steps sort the costs in the Treatment Cost table
• To Sort Data in a Query• Create a new query (Query Design) based on the Treatment
Cost table
• Include the fields (Treatment, Cost)
• Click the Sort row below the field (Cost) you wish to sort, and then click the Sort row arrow to display a menu of possible sort orders
• Click the desired sort order (Ascending)
• Run the query
• Save the query as m02q07-29-
Sorting (4 of 6)
• To Omit Duplicates• Open the query (m02q07) in Design view
• Click an empty field (to the right of Cost) in the design grid
• Click the Property Sheet button [Design tab | Show/Hide group] to display the property sheet
• Click the Unique Values property box, and then click the arrow that appears to produce a list of available choices
• Click Yes and then close the Query Properties property sheet by clicking the Property Sheet button a second time
• Run the query
• Save and close the query
-30-
Sorting (5 of 6)
• To Sort on Multiple Keys• Create a new query based on the Treatment Cost table
• Add fields such that the major key field (Animal Type) will be added before the minor key field (Cost): Treatment Number, Treatment, Animal Type, and Cost
• Select a sort order in the Sort column for multiple fields (Animal Type, Cost: Ascending)
• Run the query
• Save the query as m02q08
-31-
Sorting (6 of 6)
• Creating a top-value query allows us to restrict the number of records that appear in the query results
• To Create a Top-Values Query• Open the query (m02q08) in Design view
• Click the Return arrow [Design tab | Query Setup group] to display the Return menu
• Click an option (5) corresponding to the number of records you wish to return
• Run the query
• Save the query as m02q09
• Close the query
-32-
Joining Tables (1 of 5)
• If a query involving data from more than one table, we need to join the tables
• The tables must have identical values in matching fields
• For example you want to a query that shows the appointment dates (from the Appointments table) along with the cost of the treatment and animal type (from the Treatment Cost table)
• See Appointments table and Treatment Cost table on pg. 5 and pg. 8
-33-
Joining Tables (2 of 5)
• To Join Tables• Click the Query Design button to create a new query
• Add two related tables to the new query (Appointments, Treatment Cost)
• Add the desired fields from each table to the query (Appointment Date, Treatment, Animal Type, and Cost)
• Select Ascending as the sort order for the picked fields (Appointment Date, Treatment)
• Run the query
• Click Save button on the Quick Access Toolbar
• Save the query as Appointments and Treatments
-34-
Joining Tables (3 of 5)
• By default a joint query shows records that match all query conditions. To show results that partially match the conditions we need to change the query properties.
• To Change Join Properties• Open the query (Appointments and Treatments) 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
• Click option 3 to include all records from the Treatment Cost table and only those records from Appointments where the joint fields are equal
• Run the query
• Save the query (Save button | Quick Access Toolbar)
• Close the query -35-
Joining Tables (4 of 5)
• We can create a report based on a query that joins two or more tables (we used to create a report based on a table)
• To Create a Report from a Query• Open the Navigation Pane, and then select the desired query
in the Navigation Pane (Appointments and Treatments)
• Click the Report Wizard button to display the Report Wizard dialog box [Create tab | Reports group]
• Add the desired fields (All Fields, Click >>) to the query
• Click Next to display Report Wizard screen (3 times)
• Make sure Tabular and Portrait are selected, Click Next
• Type Appointments and Treatments Report as the title
• Click the Finish button to produce the report
• Close the report
-36-
Joining Tables (5 of 5)
• To Print a Report• With the desired report (Appointments and Treatments
Report) selected in the Navigation Pane, click FILE on the ribbon to open the Backstage view
• Click the Print Tab in the Backstage view to display the Print gallery
• Click the Quick Print button to print the report
-37-
Creating a Form for a Query
• To Create a Form for a Query
• Select the query (Appointments and Treatments) in the Navigation Pane
• Click the Form button [Create tab | Forms group] to create a simple form
• Save the form as “Appointments and Treatments Form”
• Click “OK” and close the form
-38-
Exporting Data From Access to Other Applications (1 of 3)
• To Export Data to Excel• Click the desired query (Appointments and Treatments) in the Navigation
Pane to select it• Click External Data tab -> Export group -> Excel button to display the
“Export - Excel Spreadsheet” dialog box• Click the Browse button (Export - Excel Spreadsheet dialog box) to display
the File Save dialog box• Navigate to the location (OneDrive) to save the exported file (as Excel
Workbook-“Appointments and Treatments”)• Click OK button (Export-Excel Spreadsheet dialog box) to export the data• Click the “Save export steps” check box (Export - Excel Spreadsheet dialog
box) to display the Save Export Steps options• Type the desired name (Export-Appointments and Treatments) for the
steps in the Save As text box• Click the Save Export button (Export - Excel Spreadsheet dialog box) to
save the export steps-39-
Exporting Data From Access to Other Applications (2 of 3)
• To Export Data to Word
• Click the desired query (Appointments and Treatments) in the Navigation Pane to select it
• Click the More button (External Data tab—Export Group), then click Word to display the Export-RTF dialog box
• Navigate to the location (OneDrive) in which to save the file and assign a file name (Appointments and Treatments.rtf)
• Click the Save button and then OK to export the data
• Save the export steps if you want, or just click Close without saving the export steps
-40-
Exporting Data From Access to Other Applications (3 of 3)
• To Export Data to a Text File• Click the desired query (Appointments and Treatments) in the
Navigation Pane to select it• Click the Text File button (External Data tab—Export Group) to
display Export-Text File dialog box
• Select the location (OneDrive) and name (Appointments and Treatments.txt) for the file to be created
• If you need to preserve formatting and layout, be sure the “Export data with formatting and layout” check box is checked, then click OK
• To create a delimited file, select the Delimited option button and choose your delimiter, then click Next
• To create a fixed-width file, select the Fixed Width option button, and review the position of the vertical lines, then click Next
• Click Finish to export the data• Save the export steps if you want, or simply click Close
-41-
Adding Criteria to a Join Query
• To Restrict the Records in a Join
• Open the query containing a join (Appointments and Treatments) in design view
• Type the criterion (>10) for the desired field (Cost)
• Run the query
• Close the query without
saving the changes
• The above steps modify
the query to produce
a result containing
records whose cost is
greater than $10
-42-
Calculations (1 of 5)
• A field calculated from other fields is called a calculated field
• To Use a Calculated Field in a Query – Individual Record Calculation• Create a query (Query Design) using Veterinary Vendors table• Add fields (Vendor Name, State, Veterinary Supply, Quantity,
Cost)• Right-click the Field row in the first open column in the design
grid to display a shortcut menu• Click Zoom on the shortcut menu to display the Zoom dialog box• Type the calculation (Total Cost: [Quantity]*[Cost]) in the Zoom
dialog box • Click the OK button (Zoom dialog box) to enter the expression• Run the query• Don’t close / save the query yet
-43-
Calculations (2 of 5)
• To Change a Caption in a Query
• Open the (previously created) query in Design view
• Click the field (Quantity) in the design grid to which you wish to add the caption, and then click the Property Sheet button [Design tab | Show/Hide group] to display the properties for the field
• Click the Caption box, and then type the desired caption (“Vendor Quantity”)
• Repeat the above two steps for Cost field, and change its caption to “Vendor Cost”
• Close the property sheet by clicking the Property Sheet button a second time
• Run the query
• Save the query as m02q10 and close it -44-
Calculations (3 of 5)
• To Calculate Statistics – Group Calculation
• Create a query (Query Design) using Veterinary Vendors table
• Click the Totals button [Design tab | Show Hide group] to include the Total row in the design grid
• Add the field (Cost) for which you wish to total
• Click the Total row for the added field (Cost)
• Click the Total arrow to display the Total list
• Select the desired calculation (Avg) for Access to perform
• Run the query
• Don’t close / save the query yet
-45-
Calculations (4 of 5)
• We use criteria when we only need to perform calculation for some records
• To Use Criteria in Calculating Statistics
• Return to the previous query in design view
• Add a field (Vendor Name) to the query containing statistics for which you wish to add criteria
• Click the Total row in the desired column (Vendor Name)
• Click the Total arrow for the added field to produce a Total list
• Click Where
• Type the criterion (San* for Sanchez Supplies) in the Criteria row
• Run the query
• Save the query as m02q11 (Don’t close it, yet)-46-
Calculations (5 of 5)
• Statistics are often calculated for groups of record (grouping) which means creating groups of records that share some common characteristic
• To Use Grouping
• Return to the Design view (m02q11)
• Clear the design grid
• Add Vendor Name and Cost to query
• Select Group By in the Total row for
the field (Vendor Name) used for
grouping
• Select Avg in the Total row for the Cost
• Run the query
• Save the query as m02q12 and close it-47-
Crosstab Queries (1 of 2)
-48-
• A crosstab query calculates a statistic for data grouped by two different types of information. One type forms the row heading and the other forms the column heading
• The following crosstab shows the total costs grouped by both the state and vendor name
Crosstab Queries (2 of 2)
• To Create a Crosstab Query
• Click the Query Wizard button [Create tab | Queries group] to display the New Query dialog box
• Click Crosstab Query Wizard (New Query dialog box)
• Click OK button to display the Crosstab Query Wizard dialog box
• With Tables option selected, Click Tables: Veterinary Vendors
• Click Next button
• Click State field and click add (>) to select State for row headings
• Click Next button to display next Crosstab Query Wizard screen
• Add (>) “Vendor Name” for column headings then Click Next
• Add (>) “Cost” to select the field for the calculation (Sum)
• Click Next and if needed type “Veterinary Vendors_Crosstab”
• Click Finish button and close the query-49-
Customizing the Navigation Pane
• You can change the way objects are organized on the Navigation Pane
• To Customize the Navigation Pane
• If necessary, click the Shutter Bar Open/Close Button to open the Navigation Pane
• Click the Navigation Pane arrow to produce the Navigation Pane menu
• Click the desired option (Tables and Related Views) to organize the Navigation Pane
• If needed, right-click the Navigation Pane and click Search Bar
• Type “ap” as the search string to restrict the objects displayedto only those containing “ap”
• Clear the search string by clicking “Clear Search String” button-50-