WWW.OFFICE.COM/SETUP BLOGS: INTRODUCTION
www.office.com/setup Blogs: In the previous lesson, you learned how to create a simple query with one table. Most queries you design in Access will likely use multiple tables, allowing you to answer more complex questions. In this lesson, you'll learn how to design and create a multi-table query.
Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.
Watch the video below to learn how to create a multi-table query (Part 1).
Watch the video below to learn more about joins and query criteria (Part 2).
DESIGNING A MULTI-TABLE QUERY
Queries can be difficult to understand and build if you don't have a good idea of what you're trying to find and how to find it. A one-table query can be simple enough to make up as you go along, but to build anything more powerful you'll need to plan the query in advance.
PLANNING A QUERY
When planning a query that uses more than one table, you should go through these four steps:
Pinpoint exactly what you want to know. If you could ask your database any question, what would it be? Building a query is more complicated than just asking a question, but knowing precisely what question you want to answer is essential to building a useful query.
Identify every type of information you want included in your query results. Which fields contain this information?
Locate the fields you want to include in your query. Which tables are they contained in?
Determine the criteria the information in each field needs to meet. Think about the question you asked in the first step. Which fields do you need to search for specific information? What information are you looking for? How will you search for it?
This process might seem abstract at first, but as we go through the process of planning our own multi-table query you sho
WWW.OFFICE.COM/SETUP BLOGS: INTRODUCTION
www.office.com/setup Blogs: Access offers several options that let you design and run queries that return exactly the information you're looking for. For instance, what if you need to find out how many of something exists within your database? Or what if you would like your query results to automatically be sorted a certain way? If you know how to use query options in Access, you can design almost any query you want.
In this lesson, you'll learn how to modify and sort your queries within Query Design view, as well as how to use the Totals function to create a query that can perform calculations with your data. You'll also learn about additional query-building options offered in Access.
Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.
Watch the video below to learn more about modifying queries.
MODIFYING QUERIES
Access offers several options for making your queries work better for you. In addition to modifying your query criteria and joins after you build your queries, you can choose to sort and hide fields in your query results.
TO MODIFY YOUR QUERY:
When you open an existing query in Access, it is displayed in Datasheet view, meaning you will see your query results in a table. To modify your query, you must enter Design view, the view you used when creating it. There are two ways to switch to Design view:
On the Home tab of the Ribbon, click the View command. Select Design View from the drop-down menu that appears.
Switching to Design View with the View command on the Ribbon - www.office.com/setup
In the bottom-right corner of your Access window, locate the small view icons. Click the Design View icon, which is the icon farthest to the right.
Switching to Design View using the View Icon - www.office.com/setup
Once in Desig
WWW.OFFICE.COM/SETUP BLOGS: INTRODUCTION
www.office.com/setup Blogs: If you need to share information from your database with someone but don't want that person actually working with your database, consider creating a report. Reports allow you to organize and present your data in a reader-friendly, visually appealing format. Access makes it easy to create and customize a report using data from any query or table in your database.
In this lesson, you will learn how to create, modify, and print reports.
Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.
Watch the video below to learn more about creating reports.
TO CREATE A REPORT:
Reports give you the ability to present components of your database in an easy-to-read, printable format. Access lets you create reports from both tables and queries.
Open the table or query you want to use in your report. We want to print a list of cookies we've sold, so we'll open the Cookies Sold query.
The Cookies Sold query - www.office.com/setup
Select the Create tab on the Ribbon. Locate the Reports group, then click the Report command.
Clicking the Report command - www.office.com/setup
Access will create a new report based on your object.
It's likely that some of your data will be located on the other side of the page break. To fix this, resize your fields. Simply select a field, then click and drag its edge until the field is the desired size. Repeat with additional fields until all of your fields fit.
Resizing fields in the report - www.office.com/setup
To save your report, click the Save command on the Quick Access Toolbar. When prompted, type a name for your report, then click OK.
Saving and naming the report - www.office.com/setup
Just like tables and queries, reports can be sorted and filtered. Simply right-click the field you w