Make Table
By Richard Rost
9 months ago
How to Create a Make Table Query in Microsoft Access
In this Microsoft Access tutorial, I'll show you how to create a make-table query to efficiently transform your data. We'll start by explaining how to generate a new table from existing data, addressing a query about using combo boxes to ensure users choose state abbreviations over full names. I'll demonstrate how to clean up your data list using update queries and aggregate queries to ensure accuracy before creating your new table. This tutorial is designed for those with a solid understanding of Access, aiming to improve their data management skills without needing programming knowledge.
Brendan from Springfield, Illinois (a Platinum Member) asks: How can I set up a combo box to make sure users pick state abbreviations instead of typing out full state names, like using "FL" instead of "Florida"? Also, what's the best way to sort through and clean up the existing list we have without entering everything by hand?
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, make table query, create table from query, aggregate query, combo box setup, state abbreviations, data cleanup, relational data, update query, normalization, relational combo box, primary key table setup, foreign key, relationships video, auto number, TechHelp free template, validation rule, correction table, action queries, sorting A to Z, query design tips, fixing data errors, pivot tables in access, advanced queries series, normalizing excel data, incoming data split, order and customer data segregation, state field update
Subscribe to Make Table
Get notifications when this page is updated
Intro In this video, we will look at how to create a make table query in Microsoft Access and walk through the process of transforming freeform state data into a properly structured relational table. We'll talk about cleaning up existing data with update queries, generating a unique list of values using an aggregate query, setting up a separate table for states, and updating your customer table to use state IDs as foreign keys. We'll also cover how to create a combo box in your forms so users can select from state abbreviations instead of typing them in freehand.Transcript Today, we're going to see how to create a make table query. It's where you can make a query that makes a table that you can then make other queries that can make other tables and so on. That's basically taking data from some data source, whether it's a table or another query, and then using that to create a new table. That's a make table query.
Today's question comes from Brandon in Springfield, Illinois, one of my platinum members. Brandon says, how can I set up a combo box to make sure users pick state abbreviations instead of typing out full state names, like using FL instead of Florida? Also, what's the best way to sort through and clean up the existing list we have without entering everything by hand?
I get this a lot, whether it's states, products, or even customers. You want to make that table properly relational. So instead of typing in stuff freehand, the user has to pick from the list that you provide because that list of states could be in a separate table. Unfortunately, the cleanup is usually the worst part, taking existing data and cleaning it up so it's in good shape to be in its own table. Let's take a look at what we have to do to accomplish this.
First of all, I consider this an expert-level video. What is expert? It's a standard sandwich between beginner and developer. It's beyond the basics. You should have a good solid understanding of the core functionality of Microsoft Access, but it's not quite developer. We don't need any programming to do this, and that's why I call it expert stuff. That's what I teach in my expert lessons.
Now, make table is just one type of the different action queries. There's update, append, delete, and the make table. You can modify data in a table or even create a new table using a query. Now, we are going to use an update query to clean up this list before we make the table, so go watch my update queries video if you have not yet watched it already. To get a unique list of states in the table, we're going to use an aggregate query.
These are free videos. They're on my YouTube channel and on my website. Go watch those, then come on back.
Here I am in my TechHelp free template. This is a free database. You can download it off my website if you want to. Now, in here, I've got a customer table, and state is in here, and it's just freeform text. I've typed all these in pretty well. But you're going to get people doing this if you allow them to freely enter in this stuff. You want to limit it. You could put a validation rule on here to limit it to two characters, and there's a lot of stuff you can do. But the best way to do this is to put the state data in its own state table so they have to pick from your list. If you want to add to it, you can give them ways to edit the state table. But how do we take this stuff and put that in a table without having to retype them all?
Let's say I only have 33 customers here. What if I had 33,000 customers in here? That's a pain. So the first thing you have to do is clean up this list. Now, unfortunately, there's no super simple way to do this without using an update query. What I usually do is sort the list A to Z. All right, and then I'll take a look at which ones don't match up. If you've got just one, that's no big deal to come in here and fix it. If you've got 500 people that typed in "N. Y.," you can make yourself an update query to change all of that. That just involves a little update query.
Query design, we're going to turn this into an update query. I'm going to bring in the customer table. I'm looking for the state field. The criteria are going to be New York like that, and I have to put, of course, my quotes around it. And I'm going to update it to NY. It looks like that. I missed my little dot there. And then when I run this query, all right, nothing appears to happen. But now, if I go back to my customer table, I should notice that that's been fixed.
Unfortunately, you're going to have to do that once for everyone that you want to correct. Or you can make yourself a correction table, a table that's got bad spelling for it and the good spelling for it. So if you guys want to see a video on how to do that, let me know. In fact, I have something like that that I use in my database to fix countries because I like to keep a uniform list of countries. You'd be surprised how many people type their own country wrong. Guys in Australia, I don't know, that's probably the number one country where people spell Australia wrong, as people who live in Australia. But seriously, you get regional spellings. You get people that call their country something else, or like Turkey, they spell it differently than we spell it in the United States.
I have a video on that I'm going to be putting together. You could do the same thing with states if you've got commonly mistyped states. But once you go through and clean up this list using the method that I just showed you and you've got a nice clean list in here, now we can take this data and put it into its own table. That's where today's topic comes in: a make table query.
Let's close all this. We don't have to save that. Now, to do this, we need a unique list of all of the states in the customer table. To get a unique list, we're going to create an aggregate query. Create query design, bring in the customer table, hit the totals button. This makes it an aggregate query. Now, we can bring in the state, sort it if you want to. You can see now it says group by. If you run it, there's your unique list. You should only see each state in here one time. This is another great way to go down the list and make sure that you don't have any outliers in here. You might have missed somebody putting in "M-i-n" instead of Minnesota.
If you want to get rid of null values in the criteria, put in "is not null," just like that. Now, if you run it, you'll see those null values. This is good. Let's save this as my state aggregate query.
Now we can use this guy to fill another query to make that table. Create query design. This time, we're going to pick make table query. Now, what's the new table name going to be? Let's make it state T in the current database. Now, what's going to fill it? Data from that state aggregate query. Bring that guy in here. This is the data source. Close this. I almost never used this guy. I'm not familiar with this. Bring in the state. If you want to save this, you can't. If you think you're going to be doing this on a regular basis, maybe you get a list from somebody else or you import data from some website. If you're going to be doing this, you could save this as your state make table query or whatever you want to call it. Make TQ or whatever.
When I run this, look at that. It created a table. Close this guy and open this guy up. Look at that. There's your list of states, a unique list of states in a table. The only thing that's left to do that I strongly recommend is to think every table should have an ID field. State ID. That's my auto number. You can add it to a table if it doesn't already have one. I just like to keep these guys up top. That's just a style preference of mine. I like to have the auto number, the primary key at the top of each table. Save it. Make it a primary key. Sometimes if you have an existing table and you save it, if it's a new table, Access usually asks you if you want to make a primary key. If you have an existing table and you add the field, it doesn't always, so click the key button. Make sure you get your little key there. Save it. Take a peek at what we got, and there's our data.
Now, what's left is to convert this in the customer table to this. How do you do that? Well, that's just one more update query. I'm going to go to the customer table, design view. Now, for the state, let's insert right in here, insert a row. Let's put state ID in here. That'll be a number. That's the foreign key. Remember our relationships. If you don't remember your relationships with your primary key and your foreign key, go watch my relationships video. I'll put a link down below for that. This is a one-to-many relationship. Save it.
Now, in here, there's no data in that state ID field. It's all null. Now, we got to fill it in. We have to save the state to make it X. Or if it's New York, make it Y. Whatever values in that state ID field have to go into here. How do we do that? Well, we have to match things up. This is another update query. Create query design. I'm going to bring in, I always close this thing. I'm going to bring in my customer table and my state table. Access is going to try to join it based on that ID. It sees a primary key field here, an auto number, and a number over there, and it thinks that's the relationship that you want, but it's not. Not at this point. Delete that relationship. What we need to do is link them together by state first. Then we're going to fill in that ID. Grab state over here, drag it and drop it over here under that state box. Now, we've said that, okay, this state here is the same as that state there.
If you open up, let's put in here just to see customer, the state and that state. If you run it, you'll see how it matches them all up.
All right, get rid of these. We don't need them. Now, we're going to turn this into an update query. I'm going to say what I want to do is set this value equal to that value, so it takes the ID from the state table for all the records that match by state and fills it in over here. We're going to change this to an update query. Bring down this state ID, and we're going to match it to this state ID over here, but we have to call it by its full name. We're going to come down here, and we're going to say update to state t dot state ID.
It knows that this one is from customer T because it's right there in the table reference. Now, run it. Nothing appears to happen. I have my warnings turned off, by the way. Normally, you'd see, "Oh, Access is about to update 33 rows or whatever." You could just hit "okay." I have a whole separate video where I teach you how to turn those off. I think I teach in the update query video. Now, let's go ahead and save this. We'll call this the state update queue.
You won't have to run this anymore unless you import data or get it from another source. Now, if you go into your customer table and look, look at that. You've got all matching IDs for those states. Double check. Florida should be one. New York should be 13. Let's take a peek. Florida is one. Yep. New York is 13. All right, so that worked.
Now, we can come in here. We can delete the state from our customer table. Back up your database first, folks. Just in case. I've made changes like this and then realized, "Ooh, I goofed." Make sure you make a backup first. Now, you don't have a state text field in here anymore. Now, you have to go to your forms. Wherever you got the form, you're going to get pound name errors. Now you can make a combo box right there. You can do it with the combo box wizard. I have a whole separate video on making relational combo boxes. I'll put a link down below.
But you come up here, you find a combo box. You drop it right there. You say, "I want to get the value sum of table or query." Your list of state values to pick from is in the state T. Next, bring over both fields because you need that ID. That's what we're actually storing. Sort that list by state. That's what it's going to look like. Shrink it up if you want. Now we're going to store that value in what field. We pick the state, but we actually really picked a state ID. We're going to save that value in the state ID field in the customer table. What label would you like for it? We're going to delete the label anyway. There's that label. Delete it. Slide this guy up into place. Make that zip feel a little bit smaller maybe. There's that.
Update your table. Before you update the tab order, change the name of the box. Instead of combo 30, let's call it state combo. That's on my list of pet peeves to the Access team. The wizard should ask you for a name instead of just a label. Second of all, these combo 30s in my database. Tab order. Find your state combo. Put it where it belongs. Right after city. Save it. Close it. Close it. Open it. There you go. Now you've got a properly relational state field. Pretty easy to do.
Yes, there's a bunch of steps, but sometimes scrubbing data in a database that you built, especially if you built it when you were a beginner, and lots of people do this. They learn a few things. They take some Access beginner classes. They've got tons of great ideas about a building database, and you build it a certain way, and that's fine. You build it how a beginner builds it. But then when you get into more advanced stuff, you learn relationships. Wait, that should be in a table. But now you have to go back and fix all the old data that you had in there, and that's okay. You can do it with some action queries.
Trust me. I've been doing this for 30 years, and the stuff that I learned even five years ago, I'm now going, "Wait a minute. I could have done it this way." You will constantly be tweaking your database. I think of it like the mechanic who's constantly tweaking this car.
Now, if you want to learn more about this stuff, I've got five whole classes on advanced queries.We have update queries, more update queries, append queries, delete queries, and using macros to run these queries. If you don't want to get into VBA programming, union queries, make-table queries, and all kinds of stuff. Crosstab queries, which are kind of like pivot tables in Excel. This is my action queries, my advanced queries series, my query series. Check it out, I'll put links down below.
Also, I've got a similar video on normalizing data. What does that mean? Well, let's say you pull in a table that came from a spreadsheet in Microsoft Excel. And in that spreadsheet, you've got both customers and orders.
As we know from our relationships, our tables should have only one type of data. Your customer table should not also include order data. So, normalizing it involves splitting those into a customer table and an order table. How do you match those up? That's what this video covers. Very similar to what we just did, but instead of a single field in a table, this one makes two tables out of incoming data from another table. Check this out if you want to learn more.
But that's it. There you go. That's make-table queries in a nutshell. There's so much more you can do with them. I've got like five different examples of things we can do with make-table queries, but that's going to do it for today. If you want to learn more about make-table queries, check out those videos I mentioned before or post some comments down below if you have any questions or things you want to see. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating a make-table query
Setting up a combo box for state abbreviations
Sorting and cleaning an existing list of states
Using an update query to clean up data
Creating an aggregate query for unique values
Handling null values in queries
Creating a state table using a make-table query
Adding an ID field to a new table
Linking tables using primary and foreign keys
Updating tables with matching IDs using queries
Creating a relational state field in forms
COMMERCIAL:
In today's video, we're learning about make-table queries in Access. You'll discover how to transform data from a source into a new table efficiently and explore the use of combo boxes to ensure users select from predefined lists, like state abbreviations, rather than typing freeform text. We'll tackle data cleanup using update queries and create unique data lists using aggregate queries. Plus, you'll see how to handle scenarios involving a large customer database and clean data to make it properly relational. This is aimed at those with a good understanding of Access, sitting right between basic and developer levels. If you're ready to take your database skills to the next level, this is the place for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is a make table query used for in Microsoft Access?
A. To generate complex reports from existing tables
B. To create a new table using data from an existing table or query
C. To delete data from an existing table
D. To update the schema of an existing table
Q2. Before creating a make table query, what type of query is recommended for cleaning up existing data?
A. Crosstab query
B. Update query
C. Append query
D. Delete query
Q3. When preparing to convert freeform text entries like state names to standardized codes, what is the primary challenge identified in the video?
A. Adding new entries to the database
B. Ensuring data integrity between tables
C. Cleaning up existing data entries
D. Formatting data for report generation
Q4. What is a recommended first step in creating a unique list of states from a customer table?
A. Using a delete query to remove duplicates
B. Applying a filter on the state column
C. Creating an aggregate query with a group by clause
D. Directly creating a make table query
Q5. How should the state table be connected to the customer table in a relational database setup?
A. By using a free-text relationship based on name matching
B. Through a foreign key in the customer table referencing the state ID
C. By concatenating state names into a single column
D. By manually entering state IDs in both tables
Q6. Why is it recommended to have an ID field in every new table created?
A. To store detailed descriptions of each record
B. To facilitate easy reference and joining of tables
C. To enable sorting of records in alphabetical order
D. To allow users to manually update entries in the table
Q7. What was suggested as the best practice for making sure users pick state abbreviations instead of full names in a form?
A. Implementing a validation rule for two-character entries
B. Allowing users to freely type state names as they prefer
C. Using a combo box tied to a separate state table
D. Pre-filling forms with default state values
Q8. After successfully updating the customer table with state IDs, what is the next step in refining the database structure?
A. Deleting the entire customer table and starting over
B. Backing up the database and removing the freeform state text field
C. Running an append query to add more data
D. Re-entering each state as a full name manually
Q9. What tool is suggested for those not familiar with VBA programming to run action queries?
A. Access macros
B. SQL Server Integration Services
C. Python scripts
D. Excel formulas
Q10. What does normalizing data mean according to the video?
A. Automating data entry processes
B. Consolidating multiple related data fields into one table
C. Splitting a data table into two or more tables based on data types
D. Ensuring all data entries are in uppercase
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-A; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on creating a make-table query. This is a powerful query that lets you take data from a current source, such as a table or another query, and use it as the basis for generating a new table.
Brandon from Springfield, Illinois, one of my platinum members, asked an interesting question. He's looking to set up a combo box so users select state abbreviations instead of typing full state names manually. He also wonders about the best approach to sort and clean the current list without needing to re-enter everything by hand.
I frequently encounter this issue, whether dealing with states, product names, or customer data. It's crucial to establish a properly relational table. Rather than allowing users to input information freely, they should select from a predefined list, such as a separate table enumerating states. Cleaning up existing data, unfortunately, is often a tedious task.
This lesson is what I consider expert-level content. It fits between beginner and developer, demanding a strong grasp of Microsoft Access fundamentals, but it does not require programming knowledge—just the kind of material found in my expert lessons.
A make-table query is just one in a suite of action queries, which include update, append, and delete queries as well. These can alter data in a table or even create a new one through a query, but for this demonstration, we'll utilize an update query to tidy up the list before crafting the new table. If you haven't seen my update queries video yet, it's a good prelude to what we're doing here.
Once you have a unique list of states, employing an aggregate query can help. Check out the free videos available on my YouTube channel or website for more guidance.
For this demo, I'm using my TechHelp free template database, which is downloadable from my site. The state data in my customer table is currently freeform text, leading to inaccuracies if users enter data inconsistently. To impose uniformity, you could apply a validation rule restricting entries to two characters, but housing state data in its own table is the preferable approach.
However, how do you transition the existing data into a neatly organized table without re-typing it all, especially if you're dealing with a sizable dataset like 33,000 customers? The initial step involves scrupulously cleaning up the list, often necessitating an update query to standardize state entries.
The process to create a correction table involves identifying erroneous spellings and correcting them. I have experience with similar systems for countries. For instance, Australians often misspell their own country, frequently entering diverse regional spellings or alternative expressions.
After cleaning up the list as detailed, you can use an aggregated query to create a unique list of states, thereby enabling you to construct a make-table query. The idea is to generate a new state table with all unique entries, adding an ID field for a robust design. An auto-number consistently emerges handy in ensuring each record's uniqueness. Always remember to establish this ID as the table's primary key.
The final stage involves transitioning your main customer table to reference this new state table using IDs, rather than lengthy state names. This typically necessitates another update query to correlate the existing data by linking state names to their corresponding IDs.
To wrap up this discussion, enhancing your forms with combo boxes for state selection refines the user experience, ensuring personnel or customers select only from standardized state options.
For deeper insights, I provide a range of tutorials covering advanced query types, each expanding upon concepts touched upon today. Additionally, check out my resource on normalizing data—useful for untangling combined customer and order data from Excel spreadsheets.
That's the essence of make-table queries—versatile tools that can significantly streamline data organization and quality in your databases. For more on this subject, explore the videos I've previously referenced or drop your questions and suggestions in the comments. I trust today's session has enlightened you. Live long and prosper, my friends. You can find the complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.Topic List Creating a make-table query
Setting up a combo box for state abbreviations
Sorting and cleaning an existing list of states
Using an update query to clean up data
Creating an aggregate query for unique values
Handling null values in queries
Creating a state table using a make-table query
Adding an ID field to a new table
Linking tables using primary and foreign keys
Updating tables with matching IDs using queries
Creating a relational state field in forms |
