MS Access: Creating a Table

In this tutorial we will learn how to create a small table and fill it with data. These tables will be filled in manually by us and will be maintained within the Access Database.

The two tables will be:

  • Employee Hours Billed
  • Employee Addresses


Excel Files:
Employee Hours Billed
Employee Addresses

 


Steps:

  1. Open the blank database you created earlier
  2. Go to the Create tab
  3. Click on Table

    Notice that a screen has popped up that resembles an Excel spreadsheet with a row and column grid. There is also a “Table1” in the left “Access Objects” Panel

  4. You can type in the values manually or copy and paste them. Lets do the latter to make things a bit easier
  5. Use the attached Employee_Hours_Billed spreadsheet and select the range A1:E5
  6. Copy the data
  7. Go to your Access Database
  8. Right click in the “Click to Add” column and choose “Paste as Fields”
    1. If you get a warning that the field name you provided is a reserved word, click “OK”
  9. Access will notify you on how many records you are about to add to the table (4)
  10. Press “Yes”
  11. You will now see your data in the Access table just as it was in the Excel spreadsheet.

Access_Paste_Tbl_data

You might notice that there is an ID column and an EMPLID column in our table. When you make a table in this manner, Access tries to establish it’s own unique identifier. The default is an ascending number for each row and since we already have a unique ID for our employees, we can delete the default from our table as it is not needed.

Access terms this “Unique ID” as the “Primary Key”

  1. Go to design view (far left icon) for our table, you will be prompted to save
  2. Save the Table as “tbl_Employee_Hours
  3. Select the “ID” field
  4. Right click
  5. Choose delete rows
  6. Access will double check and make sure you want to delete the row, choose “Yes”
  7. Access will warn you that you are deleting the “Primary Key”, choose “Yes”
  8. Click the “View” button (far left icon) to view our data
  9. You will be prompted for a save, choose “Yes”

Access_remove_PKey


Congratulations! You have now created your first table in Access!
Repeat these steps for the Employee Address information



Categories: Access

Tags: , ,

1 reply

Trackbacks

  1. Query Introduction and Making a Simple Select Query

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: