Join Robin Hunt for an in-depth discussion in this video Creating Access database tables from scratch, part of Building Solutions Using Excel 2013 and Access 2013 Together.
- Let's get started with our next database. From our exercise files, we'll open it up. And we're going to spend a little time working with tables by creating them from scratch. It's key to understand how a table works and the properties to check. Whether that table be generated from a link table or an imported table or whether you just created it yourself. You can learn a lot by just creating a couple of fields in the table. There are more tips and tricks in the Access 2013 Essentials course.
So, why do we need them? They're the core element of any Access solution. In order to have forms, queries and reports, we have to have tables. So, let's start by creating one from scratch. I'm going to choose to Enable the Content and I need a table of managers for my solution. So I'll go to Create. I'll select Table. Now, I can create the table design here but I would prefer to do it in the Design View so I can have the most control. Alright, click the Table1 tab and go to the Design View.
I'll name this table Managers. I do need a unique identifier. So, I will work with the ID AutoNumber that Access gives me. Then I'll add things like FirstName. It will contain text so Short Text is appropriate. I'll add LastName. Also, again, it's appropriate to have Short Text here, maybe even a little bit shorter. So, if you look below you see the Field Properties and you see the Field Size.
We likely won't have anyone with a name that exceeds 255 characters. So you could actually shorten this. Remember we do have a two Gigabyte storage limit in Access. Phone number. Phone number is also appropriate for Short Text. It contains letters and numbers. Let's work with an input mask. Here I'm going to use the wizard. So, it will allow my people to enter in the phone numbers, again, without having to type all the parenthesis and dashes but still display them.
I'll click the Build button. It's going to prompt me to save my table. I'll say Yes. And then I see some common input masks like Phone Number, Social Security Number, Zip Code. It even gives you a Try It box. If you like the way that looks, you know you're good to go. You can choose Next. It's called the Phone Number and here's the input mask that it stores. I don't really want to change it. I like it just like it is.
I'll choose Next. This question is asking me do I want to store it with the symbols or without the symbols. Again, this is a preference. For a simple end user solution, most of you would probably choose to store it with the symbols. I'll choose Next and then Finish. What's important is that when you open up any table, whether you create it yourself, it's linked or imported in, and you look at the Field Names, and the different Field Properties that exist.
You should now have a basic understanding of how to look at any table in Design View. You've seen a couple of the key areas to check. When you're working with both existing tables or new tables that you create, Access with no tables is like a day without sun. You really need the core elements of the table. You just now know that you can create them manually if they don't exist already. And now we'll move on to learning more about importing and linking.
These techniques are the key to better personal productivity data systems. Start watching now.
- Using the Problem Steps Recorder
- Importing and linking Excel data as Access tables
- Creating queries with tables
- Calculating data in Excel vs. Access
- Building forms
- Creating reports for your solution
- Building macros to run imports and queries