Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you're ready to make your first table, you can either build it from scratch or use Application Parts as a starting point. You can even save your own Application Parts for future reuse. So, let's create our first table, and we'll use Application Parts. Click on the Create tab, and here on Application Parts. Application Parts are pre- created database templates. Some include not only tables, but combinations of forms, tables, and reports as well. We're going to use this Contacts Application Part.
Go ahead and click on it. And we'll instantly have a Contacts table, three forms, four reports, and a query. Now, we're not going to be using most of these. We're going to be focusing on this table. So let's start by deleting them. To delete objects, I could click on it and right-click on it, and click Delete. But I don't have to do that over and over again. You can use a Shift-click technique. I'll click on the first one, ContactDetails, and then hold my Shift key down and click on Label. And that will highlight all of the objects that I want to delete, and then I can just hit Delete on my keyboard, and it says that this will remove them from groups, and I can't undo this. That's absolutely fine. Click Yes.
Now, I want to rename this Contacts and call it Customers. So I'm going to right-click on it, and come down here to Rename and change the name to Customers. Now, let's open it up. Double-click, and I can see all of the fields that I put in there. Most of these are going to be useful for me, but there are some that are not. I'm going to use this as a tour to show you how the fields work. First is an ID field. Click up here where it says Table tools. Click on Fields, and we can see the Data Type, which is AutoNumber.
An AutoNumber acts as a counter. It starts with 1, and every time I add a customer to it, it'll automatically increase to 2, 3, 4, et cetera. I don't have to type anything in myself. Now, note that this is not an accurate count of how many records I have in my databas,e because if I delete record 14, for example, the counter would then go 12, 13, 15. The numbers do not get reused. For us though, we need a different system. TwoTrees identifies its customers by two store initials followed by its zip code. So we need to change this field's data type from AutoNumber to Text, allowing me to enter a combination of letters and numbers.
So, let's learn more about the data types. Let's look at all of our fields. So far you've seen AutoNumber and Text. Let's talk about a couple of more. Most of these are going to be Text fields, but let's make a distinction here. Zip Code is a Text field, even though it's made up of numbers. And that's because you don't do calculations on zip codes. You don't add your zip and my zip together to make a third location. The Number data type is used for things that you have to add, subtract, multiply, and divide, like quantities.
Let's scroll- down and take a look at a couple of more. Web Page is a hyperlink data type. Hyperlinks are either for Internet URLs or for e-mail addresses. Tab over to Notes. This is a Memo field, which allows me 65,535 characters when I enter them, or 2 GB of space, if the data is entered programmatically. It also allows for rich-text formatting. Hit Tab again. The next field, Attachments, allows me to store documents, like Word files or Excel spreadsheets.
This will be a good place to save my contracts, or copies of purchase orders. And let me come back here for a moment to Notes. There are a number of other data types as well: Date/Time, Currency, Yes/No, OLE Object, and we'll see these in later lessons. Now, I want to show you something interesting. These two fields right here are Calculated, and let me show you how they come about. Let's start with our ID field. TwoTrees identifies all of the customers by two initials from their name, and then their zip code.
So, I'm going to put in GP94004, and the company name is Gino's Pizza. The owner's name is Morelli, and I'm tabbing across to go from field to field. Now, I've got just this little bit of information in, and normally I would go ahead and fill in the rest of this information, but I want to show you something. Over on the far right-hand side, I have those Two Calculated fields, and the Contact Name now became Gino Morelli, and it has File As Morelli, Gino.
Now, those are not going to be useful for me in this database, so I do want to delete them. But before I do, maybe I have an instance in the future where I do want these fields. I'm going to go ahead and show you how to save these table as an Application Part for future use. So, go to the Backstage view by clicking on the red File tab and come down to Save & Publish, and choose Template. When Template is orange, click on Save As. And I have this form to fill out.
This Name is going to be called Customer Table. I could put in a description if I'd like. It gives me an opportunity to put in a Category. I'm going to go ahead and leave this as User Templates. If I'd like to associate my own icon with my Application Part, I can put in an icon right here. These right now are grayed out. I'll put a check mark here in Application Part so that it shows up with my Application Parts menu. And when I do that, it lights up Primary Table. The table that I select here will be the default that appears in my wizard.
We only have one, so I'll choose Customers. The Instantiation Form allows you to pick a form in your part that will be run one time, right after the part is inserted, and then will be deleted when closed. This can be useful as a splash screen, or if you have a more complicated part that require some form of setup code before it can be used. If I want to include the data in that new Application Part, I can check this box. We're going to leave that blank, and I'll click OK. It says that my template has been successfully saved, and I'll click OK.
And now when I go to the Create tab and look under Application Parts, I can see my User Template right there. Now that I've saved that Application Part, let's go ahead and delete these two fields. I'll click on Contact Name, and I'll hit Delete. It will say, "do you want to permanently delete the files?" and I'll say Yes. And then I'll come over here and delete this field as well. We've made a lot of changes to this table. So, let's save it. Click on the Save icon up here in the Quick Start toolbar. So, so far you've learned how to create a table using Application Parts and some of the field properties on the Table Tools Ribbon.
Next, we'll look at building a table from scratch in the Datasheet View.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.