Bookmark and Share

Access Tables

Now that Bob has created his Access Database he can now make a table. A table is where data is stored and a table lives within a database. Without a database there can be no table!

Advertise on Tizag.com

A table in Access is quite different then a table in real life. Instead of having wooden legs and being used for meals, Access Tables are a grid made up of rows and columns. Here's an example of a table in Access:

There are for key components we want you to learn right now:

  1. tbl_Sales: The name of our table is the example is "tbl_Sales". Note that we could have simply called our sales table Sales, but by including a prefix tbl_ there is absolutely no confusion and is a great Access habit to pick up!
  2. Columns: A column is one vertical section of the table (i.e. up-and-down sections). The vertical columns have their label at the top and these labels should describe the type of information that will be stored. The columns in this table are: Employee, Product, Price and SaleNumber.
  3. Rows: A row is one horizontal segment of the table (i.e. left-to-right sections). One record takes up exactly one row. For example, in this table one sale at Bob's Shoe Store was a pair of slippers, which sold for $5.00. This record was entered left-to-right as follows: Employee-Bob, Product-Slipper, Price-$5.00, SaleNumber-3.
  4. Cells: A cell is simply the intersection of a row and a column. Can you find the cell that contains the value $150.00? Which row and column intersected at this cell? When you enter information into Access it will often be one cell at a time!

These definitions may seem confusing at first and if that is the case, please read through this lesson, play around in Access then revisit this page to seem if it is starting to make more sense. If you stick with it you'll be amazed at how much you can learn!

Now that we've covered the basics of Access Tables let's actually create one!

Creating an Access Table

When you create a table in Access you have to know what the table will store and what format that information will be in. For example if you wanted to store the product identification numbers involved in a sale, then you might label that column "ProductID" and specify that only numbers should be stored for that column.

We'll be creating the table tbl_Sales that you saw above, but remember this is only the table creation stage and we will not be entering data just yet!

  1. With the Tables object tab selected, double-click the "Create table in Design view"



  2. This will bring up the Table Design View



  3. There are three columns here that should be explained in detail:
    • Field Name: This is where you type the name for your column. A common practice is to make it one word and to use capitalization for multiple words squished into one (e.g. SaleNumber)
    • Data Type: This column is where you specify the type of data that will be stored. If you are storing money then select Currency. The most common types of data are: Text, Number, Currency and Date/Time.
    • Description: Here you can type optional notes to remind yourself or provide useful information for others who might be viewing this file later.
  4. The first column in our tbl_Sales example was Employee, so let's enter in Employee in the Field Name column and choose Text from the Data Type column. If click inside the Data Type column you will see that it is actually a drop down select box with many options to choose from. Select the Text option.



  5. Enter the following information for our remaining three columns of tbl_Sales:
    • Field Name: Product, Data Type: Text
    • Field Name: Price, Data Type: Currency
    • Field Name: SaleNumber, Data Type: Number



  6. Before we are finished here, we need to make a Primary Key. A primary key is restriction that we place on a column stating that there can be no duplicate values in that column. We will be talking about keys later, but for now right-click in the SaleNumber row and choose Primary Key from the pop-up menu.



  7. We have finished our table's outline so click the X in the top right to close the design view (don't close Access, just the Design Window).



    This will also bring up a prompt to name your Access Table.
  8. Click yes and enter "tbl_Sales" for your table's name.



Although this process of creating an Access table might seem overly complicated, with time you'll be able to create and edit existing tables very quickly. Feel free to revisit this page if you are having trouble creating an Access table.

The Next Step: Entering Data (Records)

People use Access for various reasons, but let's assume someone named Bob is using it to store information about Bob's Shoe Store. Bob gathers raw information about shoes sold from a cash register then he manually types this data into Access. Now the tricky part, of course, is where does he enter this data?

Well, before Bob can dive right into the inputting data, he must first create a Access Database that will hold his information. It's time for Bob to learn some Access (and you too)!

Creating an Access Database

Now that we have completed the construction of our table we can finally begin to enter in our sales information. The next lesson will talk you through the process of entering data into an Access table.



Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials