Create an Access Database – Create Tables and Set Data Types

by

Last updated on February 15th, 2024 at 07:38 pm

Grammarly Writing Support

If you are here, there is a good chance that you are learning how to create an Access database. Databases are everywhere. In fact, different types of databases have been in place for generations. They are used to collect and store all types of data to be easily accessed and analyzed.

Microsoft Access tables are set to look a lot like spreadsheet tables, but their purpose is quite different. Additionally, they are not as easy to create as those found in Microsoft Excel. Making these tables take a little know-how.

In this article, we will look at the following:

  • How to design a database;
  • How to create a database in Microsoft Access;
  • Creating tables in a database in Microsoft Access; and
  • Data types used in databases.

If you are new to databases, you may also be interested in checking out our article on common database terms.

How to set up your database design

Designing a database before you actually create that first table is extremely important if you wish to have an effective end product. When you are setting up your database design, you must:

  1. Determine the purpose for which you are creating a database;
  2. Find and organize the information that is required;
  3. Divide the information into the necessary tables;
  4. Turn the information items into their respective columns;
  5. Specify primary keys in the table;
  6. Set up the table relationships;
  7. Refine your design; and
  8. Apply the normalization rules.

If you are interested in learning how to use other application software, you should read our article on word-processing software to learn about fonts, types, sizes, and colors.

How to create an access database: Create a blank database

All databases are not created equal, and the process of setting up that blank database in another application may be quite different. For our database, we will focus on Microsoft Access only. When creating an access database, you need to:

  1. Open Microsoft Access.
  2. Select the blank database, or select a template from those available.
  3. Enter a name for the database, select a location, and then select create.

Tada! You have created a brand new database. Now let’s get that first table out there 😃.

If you are a CSEC student, we also have a series of articles about how to create and use a spreadsheet properly.

How to create a table in Database Access

Creating a database table in Access is not at all difficult, providing that you know the steps. I know nothing is ever difficult when you know it 🤦‍♂️😜! Well, we have you covered. Just follow these steps.

By default, when you create a database, you will already see a table created as well, but it will not fulfill the requirements for your first table, so let’s fix that. To edit that first table, follow these steps:

  1. Go to the design view by clicking on this picture on your table fields toolbar.Image of a setsquare and ruler which reresents the design view.
  2. In the popup menu that appears, give your first table a name. It is important to name the table appropriately so as to make it easier to find when needed.
  3. Click ok.
  4. In the design view, you will see three columns.
  5. Under the field name, you will need to put the names of all the columns you plan to have in your database.
  6. Next, set the data type for each field. Your data type represents the type of information you will have in that particular field. Read more about data types below.
  7. Description, while optional, is extremely important. The description is for your workmates or others who will use the database after you. It tells the person more about the data stored in a field. For instance, it lays out the specific format for your information. For instance, if the student’s ID is a six-digit number that begins with 100***, where *** represents three other numbers starting from 001.
  8. Once you have added all the required fields, save the table.
  9. Now click on the table view Image of a mini table which reresents the table view.to view your table. Thats it! You have your first table.

Now because the other tables are not already created for you, the process is slightly different for all other tables in the database. To create other tables:

  1. Go to the create toolbar, then click table design.
  2. In the new tab that opens, add your field names and data types.
  3. Continue at set 6 in the above list.

During the process of setting up your table, you also have the option of adding a primary key. To do this, select the field that you wish to use for the primary key. Next, click the key symbol on the table design toolbar. There you should now have a table with a primary key.

Are you looking to learn how to create database queries? Visit our article on an introduction to database queries, where you will learn what queries are and what they are used for. You will also learn how to set up a select query as well as how to create a query with criteria.

Data type in Access: How to set your data type

The data type refers to the value that you will enter in the fields. For instance, will your values be dates, names, and therefore alphanumeric or addresses. Each piece of data needs to have a particular data type that will restrict what can be entered into that cell.

The types of data are:

  • Numeric: This data type deals with numbers example, 12345.
  • Text: It is simply a data type that you use in a database (“Short Text” or”Long Text”), so the field with this data type has a maximum of two hundred and fifty-five (255) words.
  • Logical
  • Date/Time: It is stored as a number in a database. This data type is unique because when you put in pieces of information it can show when the date/time the data has been entered into the database.
  • Currency: This data type is in four (4) decimal places since it is dealing with financial (money).
  • Yes,/No: This data type can be either yes or no, true/false in a database.
  • Auto number: This data type is a number that increases when you add a new record to a database.
  • Hyperlink: This data type is the web address that links to a web page online.
  • Look-up Wizard: This data type is a drop-down box that gives you a limited or a certain choice of options for entry in a database.
  • OLE Object: It is a video clip, picture, or object from another program you use.
  • Attachment: It is an audio file or image, or document that you attach from another program.

Setting your data type in Access:

By now, you should already know how to set your datatype. However, in the event that you do not here are the steps.

  1. In the navigation pane, locate and double-click the table that you want to change. …
  2. Select the field (the column) that you want to change.
  3. In the Properties group, click the arrow in the drop-down list under the data type and then select the most appropriate data type for your data.

Before you go

We try our best to be as clear and detailed as possible in the information we provide. However, if you have questions or comments, be sure to leave them in the comment section below.

If you wish to learn more about databases keys, visit primary keys.