Chapter 3 – Queries
INTRODUCTION
Now we get to the fun part of data management – queries. Our data structures are in place with good table design, sound field types, and necessary relationships. Now we can use queries to perform a variety of tasks, most notably pulling data from those tables in unique ways as determined by the business scenarios.
- Create and edit simple queries to obtain data from a single Access table
- Looking at queries with data from multiple tables
Guided Exercises
From your cloud storage account, open your username-Haptown database.
You are working as a consultant for Haptown and have come to know the database design well. You have been asked to retrieve some data based on specific conditions and requirements. Perform each problem carefully, making sure the results are exactly what your manager wants.
- Haptown would like a list of all the employees in alphabetical order. Create a query that displays all employee names, showing the FirstName and LastName, but sorting in ascending order by LastName, then ascending by FirstName. Save the results as qryGE1.
- What table(s) do we need?
- What field(s) do we need?
- How do we sort in the opposite way that we need to display the fields?
- Create a query that lists the FirstName, LastName, BadgeNum, JobTitle, and RetirementAccount for all employees with a last name starting with “a”. Sort the results in ascending order by LastName. Save the results as qryGE2.
- What table(s) do we need?
- What field(s) do we need?
- How can we do a partial text filter?
- Create a query that lists the FirstName, LastName, HireDate, and BuildingName for all employees who work in an excellent condition building. Sort the results in ascending order by LastName. Save the results as qryGE3.
- Create a query that shows the FirstName, LastName, Email, and PhoneNumber for all consultants who have been assigned to a feedback submission that has a negative sentiment. Each consultant should only appear once in the results. Sort in ascending order by LastName. Save the results as qryGE4.
- What did we do to make each customer appear only once?
- What did we do to make each customer appear only once?
- Create a query that lists the FirstName, LastName, and BuildingName for employees that could potentially use any laptop computer asset. Each consultant should only appear once in the results. Sort in ascending order by LastName. Save the results as qryGE5.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
More Practice
Work you should do before our next class
Go to your IU OneDrive and open your username-Haptown database.
- The human resources department would like a list of all consults in alphabetical order. Create a query that displays all consultant names, showing the FirstName, LastName, and Email, but sorting in ascending order by LastName, then ascending by FirstName. Save the results as qryMP1.
- Create a query that lists the FirstName, LastName, and HourlyRate for all consultants that do NOT have any certifications on file. Sort descending by hourly rate. Save the results as qryMP2.
- Create a query that shows the FirstName, LastName, and Specialization for all consultants who are associated with a feedback submission with the topic of some sort of “safety”. Make sure each consultant appears only once in the results. Sort in ascending order by Specialization. Save the results as qryMP3.
- Create a query that shows the JobTitle and Vendor for all employees assigned to software that has a single license. Sort by Vendor ascending. Save the query as qryMP4.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
NOTE
You will NOT be provided with screenshots like this on assignments or exams.
Haptown ERD
