Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you want to make a new table using already existing records, you can use a Make Table action query to search for the records you want, and move them to a separate table all-in-one automated step. I am in my Customers table, and let's say I want to separate out all my customers who Retail my olive oils. Right now, both my Restaurants and my Retailers are mixed together in one table. I'll go up to the Create Ribbon, and I'll choose Query Design. I'll add my Customers table to my grid, and close this window.
In the Ribbon, go up to the Query Type and change it to Make Table. It will ask what you want to Name it, and I'll call this one Retailers, and I'll click OK. I'll extend my table so I can see more fields, and I'll double-click on all of the fields to add them to the grid. Now when I get to Type, you do have to be careful of one thing. The type field that contains the designation we need is a multi-value field, which means it can hold either the word Restaurant, the word Retail, or both.
So this regular Type field here won't work. That's what contains all of the options. Instead, I need to choose Type.Value, which separates the contents into their distinct terms; Retail and Restaurant. Then I'll go in and double-click on the rest of my fields. After you've added all your fields, scroll over to the right to your Type value. If you accidentally entered Type instead, you can drop this down and choose Type.Value off of the menu.
Click in the Criteria line, and type the word Retail. Now Run the query. It will tell you how many records will appear in the new table, and I'll click Yes. Now over in my Navigation pane is my new Retailers table. I'll double-click on it to open it, and there are all the companies that wholesale our oils. Using the Make Table query allows you to create new tables on-the-fly, automatically populating them with the fields and records of your choice.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64729 Viewers
80 Video lessons · 124331 Viewers
52 Video lessons · 60260 Viewers
59 Video lessons · 46099 Viewers