access: queries
DESCRIPTION
Access: Queries. Chapters A1-A3 All Sections. Today’s Topic. Security. Hardware & Software. IS Basics. Information Quality. Types of IS. Telecommu-nications. Systems Development. PowerPoint. Functional, Crossfunctional & Interorganizational. Database Processing & Design. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/1.jpg)
Access: QueriesAd-hoc
Reporting
Chapter T
![Page 2: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/2.jpg)
Access Queries
Queries
Access
Properties
Sorting
Selection Criteria
Calculations
![Page 3: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/3.jpg)
Objective
▪State the purpose of common table properties.
▪Create queries to support common business problems. (Using Query Design View only)
▪Distinguish between record and summary calculations.
![Page 4: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/4.jpg)
What are Access Objects?
▪Tables
▪Queries
▪Reports
▪Forms
![Page 5: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/5.jpg)
Open XLS in Access – Step 1Open blank desktop database & name it
![Page 6: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/6.jpg)
Open XLS in Access – Step 2Load data into new table
![Page 7: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/7.jpg)
Open XLS in Access – Step 3
![Page 8: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/8.jpg)
Open XLS in Access – Step 4Format Text Fields
![Page 9: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/9.jpg)
Open XLS in Access – Step 5Add Primary Key
![Page 10: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/10.jpg)
Open XLS in Access – Step 6Name Table
![Page 11: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/11.jpg)
Example Data
Participant Registration Card
Participant ID: 1
Age:
Gender: Male Female
Are you married? Y or NAre you a parent? Y or NAre you a home owner?Y or N
What is your favorite food?
![Page 12: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/12.jpg)
Example Data
Observation Card
Participant ID #:
Observation Date:
1 = Poor … 5 = Excellent
Rating of Product A: 1 2 3 4 5Rating of Product B: 1 2 3 4 5 Rating of Product C: 1 2 3 4 5 Rating of Product D: 1 2 3 4 5
![Page 13: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/13.jpg)
Open Existing DatabaseStart Access
![Page 14: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/14.jpg)
Review Tables
![Page 15: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/15.jpg)
Review Relationships
![Page 16: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/16.jpg)
What Table Properties Can I Set?
▪ Field Name
▪Data Type
▪ Field Size
▪ Format (output)
▪Decimal Places
▪ Input Mask
▪Default Value
▪ Validation Rule
▪ Required
▪ Indexed
![Page 17: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/17.jpg)
Book Database
![Page 18: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/18.jpg)
What is the Purpose of Queries?
▪Ad-hoc information retrieval
▪Output subsets of data
![Page 19: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/19.jpg)
Start a QueryExample: List demographics of all participants.
![Page 20: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/20.jpg)
Fields
![Page 21: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/21.jpg)
Run a Query
![Page 22: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/22.jpg)
Show & Hide FieldsExample: List demographics of all participants
but hide homeowner field.
![Page 23: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/23.jpg)
Save a Query
![Page 24: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/24.jpg)
Sort RecordsExample: List ratings for Product A from high to low.
![Page 25: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/25.jpg)
Select RecordsExample: List demographics of Female participants.
![Page 26: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/26.jpg)
Query More Than One Table
Example: List all observation ratings by female participants.
![Page 27: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/27.jpg)
Selection Criteria: OperatorsExample: List demographics of all
participants over 30 years of age.
![Page 28: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/28.jpg)
Selection Criteria: * and ?Example: List demographics of all
participants who like liver. Liver may be anywhere in the Favorite Food string; beginning, middle or end.
![Page 29: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/29.jpg)
Selection Criteria: NOTExample: List demographics of all
participants who did not identify ice cream as their favorite food.
![Page 30: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/30.jpg)
Selection Criteria: NULLExample: List demographics of all
participants who did not identify a favorite food.
![Page 31: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/31.jpg)
Selection Criteria: ANDExample: List demographics of female
participants who are over thirty.
![Page 32: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/32.jpg)
Selection Criteria: ORExample: List demographics of participants
who are either Female or over 30.
![Page 33: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/33.jpg)
Combine Selection CriteriaExample: List all female participants who
are married and all male participants who are parents.
![Page 34: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/34.jpg)
Selection Criteria: DatesExample: List all observations
recorded on Feb 24, 2011.
![Page 35: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/35.jpg)
Summary CalculationsOne calculation total for a group of records.
Example: Calculate the average rating each participant assigned to Product A.
![Page 36: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/36.jpg)
Record CalculationsOne calculation per record.
Example: Determine the age of each participant in five years.
![Page 37: Access: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062501/56816940550346895de0c173/html5/thumbnails/37.jpg)
Query Limitations▪Queries can show only one level of grouping at a time.
▪Queries have limited formatting options.