In this video, discover the basic of Access tables and how they relate to other objects, like queries.
- [Narrator] You can learn a lot just by creating a couple of fields in a table, there are a lot more tips and tricks on table design in the Access essentials course. Why do we even need tables? They're the fundamental element of any database. They're used by forms, queries, and reports. With no tables, there's really no need to have anything else. There are multiple ways to get tables in your Access database. You can create them directly. You can also create tables by importing data from Excel or CSV files.
You can also generate tables by linking them from other data sources or other databases. Understanding how tables are generated can help you work better with Access. It's important to understand some basic table structure. First of all the AutoNumber. Any time you create a table in Access, it automatically gives you an ID field that's set to AutoNumber. The AutoNumber is unique for each record, and is consecutively numbered. It's also important to understand that everything in Access has its own field properties.
Those properties control everything about that particular field. There's a few I check in every database. Like the field size. The field size actually controls the number of characters that are allowed in a field, and it's a great way to maintain the right amount of space for a field. The format, that controls how a field may be displayed. When you let users enter data, they enter it all sorts of ways, uppercase, lowercase. The format can control the field display.
And then there's the input mask. The input mask not only controls how it looks, but actually how it's entered. You also have fields like caption, so we name it one way, but we want the users to see it another. And then the default value, the value that shows up in the field by default. Let's go take a look. In our database, let's go ahead and create a table. I'll go to create, I'll choose table.
It defaults to the datasheet view, but I like to build my tables in design view. I'll right-click table one and choose design view. Let's go ahead and name this managers. And again you see the default ID field, with the AutoNumber. Let's start with first name. I'm going to type that first name with no spaces, and when I tab it gives me the default data type of short text. Again, you can hit the drop down to look at all the different data types.
Notice the field size below. It defaults to 255. It has to hold 255 characters whether it uses it or not. So if we're trying to capitalize on that two gig space, why don't we change this to 75? I'll go create last name. Again it defaults to short text automatically, with 255 characters, and we'll change that to 75. We have first name and last name named with actually no spaces, so let's click on first name and adjust the caption.
We want other people, when they look at the database to see first space name. And the same thing for last name. I'm going to add the phone number. Again it defaults to short text. Below, I'm going to use the input mask. So in the field properties I'll place my cursor in input mask and I'll click the ellipse button to the right. It prompts me to save the table. I'll go ahead and say yes to that, and pull up the phone number.
The input mask gives us several options. You can click through each one if you like. We'll choose phone number. This allows us to build a mask so that the data will look a certain way, but also only allow it to be entered a certain way. I'll go ahead and choose next. Each one of these numbers means something different. You can learn more about input mask in the help menu. So I'm going to go ahead and try it. Again, you notice how it pops up.
I'll do just a sample number here. And again, all I had to do was key in the number. I didn't have to hold shift and include it in parentheses. I also didn't have to type the dash. I'll go ahead and choose next. Now, this becomes a decision, do you want to store it with the symbols or without the symbols? I want to save space, so if I store it without the symbols, it doesn't create all those extra characters. But if I want those symbols to show up, well, then I can use them.
Let's store it without the symbols. I'll go ahead and choose next, and then finish. Alright, awesome. Everyone in the organization has the same telephone number. So I'm going to go and create office phone. And then in the default value, I'm going to establish the default value. Right here I'm going to do open parentheses five five five, close the parentheses, five six seven nine, three oh nine.
Let me go add my dash in there. Perfect. Let me go ahead and save that. It told me that I had invalid syntax, okay so I'm going to go in and put some quotes around that, cause I literally want that to show up. Perfect. Now I should be able to save it. Now, I've created my first table, let's go take a look at it. In the datasheet view, I'll right-click the managers tab up top, and then I'll left click on datasheet view.
Notice that I see first space name, last space name, phone number, and then I see that default value for the office phone. I'll go ahead and save that. With database, what you see isn't always what you get. It's very important to look at the different field properties, review the table design, and take a look at the data types.
- How Excel and Access can work together
- Using the Problem Steps Recorder
- Leveraging screen capture tools
- Building tables in Excel and Access
- Creating basic queries using tables
- Maintaining linked tables
- Calculating in Excel versus Access
- Building forms
- Building macros to run on-demand queries
- Generating datasets using parameters, queries, and macros