How To Create A Make Table Query In Access: Streamlined Data Management

I remember the first time I needed to create a permanent table from a query result in Access. The solution was simpler than I expected, and it saved me hours of manual data entry every week.

This approach lets you take query results and turn them into a standalone table with just a few clicks. No complex SQL knowledge required, and you can even automate the process to run on a schedule.

You’ll end up with a clean, new table containing exactly the data you need, without touching your original datasets. The whole process takes under 5 minutes once you know the steps.

Works for any Access version from 2010 onward. Here’s how to set it up properly.

What Youll Need

Instructions

Here’s the procedure I follow to create a new table from query results quickly and reliably.

Launch Microsoft Access and open the database containing your source data. I like to start from the Navigation Pane on the left—it keeps everything organized.

Click the Create tab in the ribbon, then select Query Design. This opens a blank query with the Show Table dialog. Select the table or tables containing your data, click Add, then click Close. The tables appear as small windows in the design grid.

Drag the fields you want to include in your new table from the source table into the query design grid. You can arrange them in any order here—the order in the grid determines the column order in the resulting table.

Add any criteria in the Criteria row to filter which records get copied. For example, entering >#1/1/2024# in the Criteria row under a date field will only include records dated after January 1, 2024. Run the query in Datasheet View first to verify you’re getting the expected results.

With the query still in Design View, click the Make Table button in the Query Type group on the ribbon. A dialog appears asking for the new table name. Type a descriptive name like FilteredCustomers2024—this helps you identify it later.

Choose whether to create the table in the current database or another open database. For most cases, select Current Database and click OK. The query type changes to Make Table, and the design grid now shows a new row labeled Table with your table name.

Click the Run button in the Results group. Access prompts you to confirm the action with a warning that you’re about to create a new table. Click Yes to proceed.

Access creates the new table and populates it with the query results. It also displays a message confirming the number of rows copied. Open the Navigation Pane and locate your new table—it appears with the name you specified. Verify the data by double-clicking to open it in Datasheet View.

Check that all expected records are present and the data looks correct. I always scroll through the first and last few rows to catch any obvious issues early.

If you need to modify the structure, switch to Design View for the new table. Here you can add indexes, change data types, or set primary keys. For large tables, consider adding indexes on fields you’ll frequently query to improve performance. The make table process preserves the data types from the source, but you can adjust them here if needed.

Tips and Common Mistakes

These practical pointers will help you avoid common pitfalls when working with make table queries.

The Field Selection Trick: In Step 2, when dragging fields into the design grid, remember that the order here determines your final table’s column sequence. I learned this the hard way when my date fields ended up scattered between customer names. Take a moment to arrange them logically before moving on.

Criteria Verification: Always run your query in Datasheet View before converting to a make table, as mentioned in Step 2. Confession: I skipped this once and accidentally copied 10,000 extra records. The preview step catches most mistakes and only takes a few seconds.

Naming Convention: In Step 3, use descriptive names like the example suggests (FilteredCustomers2024). Between us, vague names like “Table1” will haunt you months later when you’re trying to remember which table contains which data. Include the date or purpose in the name.

Post-Creation Check: Step 5 mentions scrolling through records, but I also recommend checking the field data types. The make table process preserves source types, but sometimes you’ll want to adjust a text field to a number field or add a primary key to prevent duplicates.

Frequently Asked Questions

Creating a Make Table Query in Access is a powerful way to generate new tables from existing data. 💡 With practice, you’ll streamline your database tasks and improve efficiency. Next, try applying these steps to a real project to reinforce your skills!