Additional field properties

- [Instructor] There are a few other field properties available in the table design view that are worth exploring. So let's take a look at those using the guests table. I'll go ahead and select it and open it up into design view. The first one I wanna look at is the field size property. Right now, I'm selected on the GuestID field. We're taking a look at the field size, and it is set to a long integer. This will allow new values to increment in whole numbers from one to two to three and so on. Long integer fields store whole numbers only, no decimals and include a range from about negative two billion to positive two billion so we have plenty of space to grow in this field.

For a short text datatype such as our FirstName field, we can see that the field size is listed as 255. This is the number of characters or the maximum number of characters that we can store in this field. Now, if you know that you're only going to need a certain number of characters, you can make your databases more efficient if you specify a lower number here. For instance, in the state field, right now it's set at 255 characters, as well. For this particular database, I know that I'll only ever need to use three characters. If I leave it set to the default of 255, access blocks out enough space to store 252 characters that I'll never ever use, and it does this for every single record in the table.

If I make the change here and change it to just three, we're gonna save up a lot of space in our database and make things more efficient and faster. The next property I wanna look at is the caption field, which is this one right there. This will allow you to specify a header name that's different from the official field name that's stored here in the table. This can be useful when setting up forms, as the labels that get auto-created will use the caption instead of the field name text. However, setting up a caption here often just creates confusion down the line, since the true field name is essentially masked from view.

This can make making references more difficult to set up. So my recommendation is to avoid using the caption property here. We'll see better options later on. The required property is another one I wanna take a look at. This will allow us to force a field to be entered. Otherwise, the record won't be saved. The primary key field is always required, since it's the one field that keeps the entire database working properly. On this guests table, I'm going to set the first name and last name to also be required, since that's the minimum amount of information I wanna store in this table.

I'll go ahead and select the FirstName column here and then come down to required and change it from no, I'll use the dropdown menu on the right, to change it to yes. We'll do the same thing for the LastName field. Go and click there, come down to required, and again switch it to yes. We can also establish additional indexes on the table. Now, again, the primary key field is already indexed, but you can create additional indexes, if needed. Having additional indexes on the right fields can help speed up searches and queries. Think about it like this. If I give you a phone book and a person's name, you can quickly look up the person's phone number, since the phone book is indexed by last name.

However, if I give you a phone number and the same phone book, imagine trying to find who that number belonged to. You'd have to search through every line in the book to locate the correct person. However, if we had a reverse directory, which is a phone book that's sorted by number instead of name, you would easily be able to locate the phone number fairly quickly. Adding additional indexes is similar. However, the drawback is that every time you add a new record, all of the indexes have to be rebuilt. There's a definite trade off, and you'll have to experiment with your data tables and queries to identify the optimal number of indexes for your database.

Now, one of the things that I know that we'll do often in this database is to search for a guest based off of their last name. So it makes sense that we might wanna add an index to the LastName column. To do that, I'll just go ahead and make sure that we're still selected on the LastName field. We'll come down to the index property, and I'll change it from no to yes. Notice that I actually have two different options for yes. I have yes, duplicates are okay, and yes, no duplicates. Now, because it's likely that I might have two guests with the same last name, I'm gonna make sure I choose the yes, duplicates okay option. So those are the properties that I wanna take a look at.

Now, you might have noticed that, as we clicked through these properties, the text on the right has been changing to give us a brief description of the various properties. It also notes that you can press the F1 key to bring up the help document for the specific field, which will give you additional instructions on how to apply that property to your data tables. You should continue exploring the properties for various data types. Different data types will have additional properties associated with them. Many are self-explanatory. For instance, with numbers, we can choose for integers or decimals, but I'm gonna admit that some of them, such as a property called IME Sentence Mode, might be so obscure that you're never gonna have a reason to adjust it.

However, if you ever have a question or would like some additional resources on the properties, simply click in the box and then press that F1 key to quickly get the help sheet.