Microsoft Access Subforms: The Key to Displaying Access Data from More than One Table
Want to take a deep dive into Microsoft Access? Consider enrolling in one of our virtual, full-day Access training classes. All classes are instructor-led, encourage interaction and questions, and provide hands-on exercises and learning aids.
A frequent question asked in our Access classes concerns how to display the data in a form when the data is coming from more than one table. For instance, how can a form show each customer along with the many orders placed by each customer? The solution is to create a subform in a form.
To try out this process, download this “Access Subforms” sample data file (ZIP). The Customer table and Order table are already created for you, so you can focus on creating the subform. NOTE: You must have Microsoft Access installed to use the sample files.
Begin by identifying the tables you’ll use. For this example, we’ll use an Order table containing order data and a Customer table that contains customer data.
You must establish the relationship between the two tables. This relationship is a one to many, because each customer can place many orders, but each order is associated with only one customer.
The Simple Approach to Creating Microsoft Access Subforms
Once you’ve established the table relationship, Access makes it easy to create a subform. Highlight the Customers table, and click the Form button in the Forms group on the Create tab. Access will assume the Customer table fields should be placed in the new form because it is selected. The Form button will add the subform for us. However, you may need to do some rearranging to get the view you’re looking for.
Creating Access Subforms for More Elaborate Data
If you find that your form isn’t displaying as you hoped, there is another method for creating a subform that works better when your data is more elaborate. After creating the relationship, follow these steps:
- Build a form based on the Customer table so that it displays the needed fields from the table. Construct this form using the Design View, so the fields can be rearranged into a sensible layout.
- While in Design View, add the subform using the Subform tool in the Controls group in the Design ribbon. Extend the form in length to provide room for the order list. The Subform Wizard will open and help ensure the subform works properly.
- Identify the source of the data to be displayed in the subform. Select the Orders table and click Next.
- Choose the Orders table, add all the fields to the subform by clicking the double chevron button, and click Next.
- The Subform Wizard will show how the two tables are related, using the Customer ID field. Because you previously built the table relationship, the Subform Wizard will always know how this should be done. Click Next.
- Give the subform a name, such as “fsubOrders,” and click Finish.
- Switch the form to Layout View so the subform can be resized appropriately. The resulting form shows all the orders for each customer.
Share this Access Tip!
Did you find this Access Subforms tip valuable? Be sure to share it using the social share icons on this page!
Enroll in Our Next Microsoft Access Training Class
To dive in more deeply into Access or to prepare for the Microsoft Office Specialist (MOS) Certification exam for Access, register today for one of our Microsoft Access 2016 courses. These open enrollment courses are currently being held virtually:
- Access 2016: Part 1 — In this course, you will learn how to use Access 2013 to manage your data, including how to create a new database, construct tables, design forms and reports, and create queries to join, filter, and sort data.
- Access 2016: Part 2 — You will expand your knowledge of relational database design, write advanced queries, structure existing data, share data across applications, and customize reports.
- Access 2016: Part 3 — Geared to current or future database administrators, this course teaches advanced Access features such as database management, advanced form design, packaging a database, encrypting a database, preparing a database for multi-user access and more.
Contact our Training Team for more information.
Last updated: 09/08/2020
Subscribe to Monthly Training Updates
Receive monthly productivity and training insights, software tips, and notices of upcoming classes!