Access requires that the columns, or fields within tables all store the same type of information. Before we can create our tables, we’ll need to define the types of data that we want to store. In this movie, Adam will discuss the difference between text and numeric data types, and introduce the concept of Primary Keys and AutoNumbers.
- [Voiceover] When designing our data tables in Access, we'll need to create the columns that will store the attributes for each record within the table. A major part of that design process is choosing the kinds of data that the column will store. In order to maintain a consistent and predictable storage environment, the data type will ensure that the values that are entered meet your expectations. Access supports a variety of data types, and I'd like to just touch on some of the more common ones that you'll use. For text-based data, you'll have two choices. You can either store the data in a short-text format or a long-text format. Short text will allow you to input letters, digits, and punctuation up to 255 characters in length.
The long text type will allow you to store larger blocks of text, and tops out at over 65,000 characters in length. In addition, the long text data type allows you to store rich text formatting, such as font colors and italics, something that the short text data type does not support. When storing numerical data that could be used in calculations, you'll wanna specify one of the number data types. In Access, you can choose from types that will store whole numbers only, such as the integer or long integer, or types that will store decimal values, such as single precision floating point, double precision floating point, or decimal.
Access also supports a few specialized data types for storing currency, date and time data, or file attachments. We previously saw the date and time data type when we chose due dates for our task list database. They come with a calendar-picker tool that makes it easy to select dates from a pop-up calendar. We also saw the attachment data type in use when we added a photo of Mark to his contact page. These data types can add lots of flexibility to your database projects. Now when we start creating our tables, there's one additional requirement that we'll need to decide how to handle. Every table is required to have something called a primary key.
The primary key is a column that's used as a unique identifier for every record in the table. It's that one piece of information that will lead you to exactly one record in the table. Some data tables will have a suitable attribute that can serve as the unique record identifier. These are called natural keys. For instance, at the Landon Hotel, each room has a unique room number, and no two rooms will ever have the same number assigned to them. We can use the room number as a primary key for the room data. If your table has no suitable key already present in the data, though, Access provides one additional data type that will be useful; it's the AutoNumber data type, and it essentially creates an internal serial number for each record that you add to the table.
As an example, when it comes to the hotel's guests, I can't guarantee that we'll never have two guests with the same first and last name. In this case, we can use an AutoNumber to give each guest a unique identifier in order to keep their visit history and room charges distinct from one another. Now that we know a little about how to define the types of data that we wanna store in our database, we can put that knowledge to work when we create our own tables. We'll do that next.
If you need a deeper dive into the topics discussed, check out Adam's Access 2016 Essential Training course.
- Loading a premade template database
- Exploring the interface
- Defining data types
- Creating tables
- Defining table relationships
- Editing forms
- Creating queries and reports
- Editing reports in Layout view