How to Add Tables in Microsoft Access

In Microsoft Access, tables are the foundation of the database. While creating an access database, we store data into the tables. For Example, you can create a database to store various information, including names, Mobile number, email address, home address, etc. With a single table, one can create forms, queries, and reports as well. You can later edit, calculate, filter, and search a set of database recorded from these tables. Now, we know how useful these Microsoft Access tables are for keeping records of miscellaneous data, it seems evident that most of you want to know all the ways to add tables to the existing ones.

Here, in this article, we will learn how to create a table in Microsoft Access? How to add tables in Microsoft Access? How to add fields in a table, set primary key and other important table properties. But before we move into that, lets first learn what a table in Microsoft Access is?

Microsoft Access

What is a table in Microsoft Access?

A table is an organized structure that holds various information in a particular format that becomes easy for anyone to understand. A table consists of fields (single columns within it) that have categories of data. Each record or value in the field represents a particular category of data with a destined name given to the fields, and no two different fields can have the same name. For instance, a table might have four fields with each field, including Name, Email, Mobile Number, and Address. In every row of the table, each field consists of the destined information and that rows called records.

Add tables in Microsoft Access.

Exploring tables to build up connections is not at all problematic but a tricky part of Microsoft Access. Though now Microsoft has worked to improve it and made it significantly more straightforward. The new add table feature has got added to increase your productivity and enhance the user experience.

How to Use the New ‘add table’?

For adding new tables to your existing database, follow the steps given below:

  1. On the Ribbon, select the ‘Database tool’ and then select ‘All relationships.’
  2. In the right to your screen, find the ‘Add table’ pane. (In case you do not see it, right-click and then select ‘Show table,’ and it will appear.

How to add a new table in the existing database?

  1. Firstly, on the Ribbon, click the ‘Create tab.’
  2. A list of other tabs will open. Click on the table button, and a new table will appear (stating “click to add”).
  3. Press “Click to add,” and a drop-down list will appear through which you can select the desired column format. For Example, if you want to store the product’s name or client’s name, select the format “Short text.”
  4. Now build and name the field for the newly added table like “Product Name” or “Client Name.”

For adding more such tables into your database, repeat the first and third steps.

How to rename or customize the ID field section?

Here once you look at the access table, you will notice an additional column/field (named ID) created automatically in the table. This ID table is an employee table, which is a unique identifier for our employees. It contains a number that represents the primary key.

A Primary Key is used to identify each record mentioned in the table.

The ID field which has already got created can get renamed to suit your conditions. For that, follow the steps given below:

  1. From the Ribbon, select Fields name and caption.
  2. A dialogue box stating “Enter filed properties” will appear. It will consist of three fields, including name caption and description.
  3. Now change the name to ‘employees ID’ for making it more specific and click “OK.” You can also add the other optional information if you want.

The ‘ID’ filed now gets replaced with the caption ‘employees ID’ and gets automatically set to number, so it doesn’t require us to set any data type.

  1. Once all the required tables have got added, click on the ‘Save’ icon or press Ctrl+S all together while you see the “Save as” dialogue box enter your database name and click OK.

Field data types you can assign while creating or adding a table.

  • Short text: It contains text or a combination of text, number, and punctuation. Max. Length of 255 characters.
  • Long text: It contains text (longer version). Max. Length 65,535 characters.
  • Date & time: Contains time and date code.
  • Number: Numeric data on which you want to perform any calculation.
  • Yes/No: Logical type data true/false, Yes/No, On/Off. Only with 2 possible values on the field.
  • Currency: Number data formatted as currency.
  • Auto Number: It gets useful, just like the primary key. It assigns a unique number ID for all records in the table.
  • Calculated: It lets you create a calculation field.
  • Look-up wizard: It guides you through setting up a lookup field.
  • Hyperlink: Hyperlink to a WWW address.
  • Attachment: It allows you to attach any file. (Desktop only).
  • OLE object: It allows you to connect with objects in the windows application. (Desktop only).
  • Image: It gets used to store image data. (Web App only).

How to delete a table in Microsoft Access

In case you no longer want a table, you can choose to delete it.

Important Note: You cannot delete a table while it is still operating (opened). So close the table that you need to remove and follow the steps given below:

  1. Firstly, go to the home tab from the Ribbon.
  2. Now under the all-access tab, select the table that you want to delete. For Example, select ‘Employees ID.’
  3. Press the “Delete” button on the top tab.
  4. A dialogue box “Microsoft Access” will appear. Now press “Yes” to delete the proceeding.

In case you have deleted the wrong file, press CTRL+Z altogether or select the undo button from the top tab.

We just discussed and learned about “How to Add tables in Microsoft Access” using two different steps. Both the steps mentioned above are tried and tested and will prove helpful for your requirements. In case you want to delete a table in Microsoft Access, we have also provided for the same above.

We have tried to provide all relevant information about adding tables in MS access in the article above. You can apply the same steps in all versions of access. We hope you will find all the information mentioned above helpful. After reading this article, if you have any queries or feedback, please write down the comment in the below comment box.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.