- Tables provide the foundation of any good database. And the success of the database, how well it functions and how flexible it is to future growth often comes down the structure of the tables that house your data. Understanding how tables function in Access is key to creating a well-structured database. Tables in Access follow a very strict model. Their horizontal rows are called records, records are an unbroken chain of properties that describe a single entity. For instance, in an employees table you would have a record for each individual in the company. Fields are the vertical columns.
Each field in a table is a single property that is being tracked for each record. In a well designed Access table each field stores just a single piece of information, such as a first name, instead of the entire full name. Each field can hold data of a single data type. You can choose to store short text, up to a maximum of 255 characters, or long text, with a maximum character limit of just above 65,000. This is useful for comments and memos. We can also store numerical values that you would perform mathematical calculations with.
These can be set to integers only, or can include exact decimal values, or even floating point numbers. There are data types that store date and time or currency values. AutoNumber fields create an incrementing serial number field that are automatically generated for you when new records are added to the table. Yes and No is a binary field. It can be yes or no, true or false, or on or off. OLE Objects is an older format, it's used for attaching files, such as images or Word documents. If you need to attach files though I recommend using the newer and much more efficient Attachment data type.
Really the OLE Object data type is just kept around for backwards compatibility and probably shouldn't be used at this point. If you'd rather not import files into the database you can choose to use a hyperlink data type instead and provide a link to external content. This could be files on your network, email addresses, or locations on the internet. Finally, the Calculated field creates data by calculating it from one or more fields in your data tables. And the Lookup Wizard will help you create a drop down list to limit the available entries. We'll look at using the Lookup field in an upcoming movie.
In addition to the fields that describe each record's properties we'll also create fields that link tables together. Previously I've mentioned that Access stores data in related tables. Because of this each record in the table needs to be uniquely identifiable. In order to ensure that this is the case we'll create a field specifically for this purpose called the primary key. The primary key is typically a serial number or other unique identifier. Think about how many unique identifiers you have for the various databases that you personally appear in. You probably have a drivers license number, and a library card number, a social security number, a checking account number, and the list goes on and on.
The reason that all these systems use serial numbers instead of just your name is so that they can guarantee an unique individual. It wouldn't be very nice if one Mary Smith had to pay the fine for another Mary Smith's overdue library book just because the library couldn't tell them apart. The primary key is also going to be our link to finding related records in additional tables. In the related table they'll be called the foreign key. The process of breaking your data into related tables is called Normalization. Normalization rules fall into several categories called Normal Forms.
Your database is said to satisfy the First Normal Form, or 1NF, if all of the cells contain only a single value. Rather than have single employee name field we should break that into separate first name and last name fields. Breaking addresses into separate street, city, state, and zip code fields is another common example. The Second Normal Form states that only data that is dependent on the primary key belongs in that table. We wouldn't want to store information about the customers that each employee has helped in the employees table. Instead we should create a new table that describes the interacts between our employees and our customers.
The Third Normal Form states that the data can be calculated or derived from other fields should not be stored in the database. We wouldn't want to store fields for employee initials for instance because we can easily calculate them by taking the first letter from the first name field and the first letter from the last name field. Tables are where your data lives and are the foundation of your database. Creating a strong foundation as a base and then growing your database on top with additional queries, forms, and reports will mean that your database will be able to be flexible and efficient.
Author
Released
9/22/2015The course also shows you how to build queries and action queries, create and design forms, use macros, integrate Access with the rest of the Office 365 suite, and maintain your databases over time.
- Creating a new database
- Creating tables and new data types
- Importing and entering data
- Setting up relationships and primary keys
- Adding validation rules
- Sorting and filtering table data
- Building queries
- Designing forms
- Creating reports
- Attaching macros to buttons and tables
- Working with Excel and Outlook data
- Maintaining an Access database
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2016 Essential Training
with Dennis Taylor8h 53m Beginner -
Office 2016 New Features
with David Rivers47m 33s Intermediate -
Outlook 2016 Essential Training
with Jess Stratton2h 49m Beginner
-
Introduction
-
Welcome1m 25s
-
-
1. Getting Started
-
Key database concepts4m 14s
-
Using the Navigation pane4m 47s
-
Using the Tell Me feature1m 47s
-
Making backups2m 14s
-
Accessing Access help2m 15s
-
-
2. Creating Tables
-
Entering data4m 21s
-
Importing tables5m 37s
-
3. Setting Field Properties
-
Setting the default value2m 35s
-
Creating lookup fields3m 51s
-
Solution: Creating tables5m 50s
-
4. Organizing Records
-
Formatting columns3m 55s
-
Sorting table data3m 48s
-
Filtering table data3m 22s
-
-
5. Using Queries
-
Building expressions7m 26s
-
Solution: Creating queries4m 38s
-
6. Working with Specialty Queries
-
Using update queries6m 44s
-
Using make table queries3m 33s
-
Using delete queries4m 13s
-
Using append queries3m 50s
-
-
7. Creating Forms
-
Generating forms from tables4m 37s
-
Using the Form Wizard4m 35s
-
8. Designing Forms
-
Setting data sources6m 8s
-
Understanding input boxes5m 24s
-
Record navigation4m 2s
-
Adding buttons4m 21s
-
Assigning a tab order4m 36s
-
Creating a navigation form4m 25s
-
Solution: Creating forms4m 29s
-
9. Creating Reports
-
Introducing reports4m 19s
-
Using the Report Wizard4m 53s
-
Creating calculated totals3m 52s
-
Creating labels5m 21s
-
Adjusting print settings4m 6s
-
Solution: Creating reports5m 29s
-
-
10. Working with Macros
-
Creating macros6m 9s
-
Launching macros at startup2m 36s
-
-
11. Integrating Access with the Office Suite
-
Exporting tables to Excel3m 17s
-
Emailing with macros4m 13s
-
-
12. Maintaining the Database
-
Documenting your work4m 11s
-
Creating custom Ribbon tabs2m 57s
-
Conclusion
-
Next steps1m 2s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Understanding table structure and relationships