MS Access Notes - Tables

Objectives: MS Access Notes - Tables

MS Access Notes - Tables

Microsoft Access Components - Tables

1. What is a Table?

A Table in MS Access is used to store raw data in a structured format. Data is stored in rows (called records) and columns (called fields).

Key Terms:

  • Field: A column in a table that holds a specific type of data (e.g., Name, Age, Phone Number).
  • Record: A row in a table containing information for each item/person.
  • Primary Key: A field that uniquely identifies each record (e.g., Student ID).

2. Real-Life Example of a Table

Imagine a school database:
StudentID FirstName LastName Class DateOfBirth
101 John Doe Form 1 2008-05-12
102 Jane Smith Form 2 2007-11-23
This table stores all student details in an organized way.

3. Practical Steps to Create a Table in MS Access

Step 1: Open MS Access

Launch Microsoft Access and choose Blank Database. Give your database a name and click Create.

Step 2: Create a Table

You have 3 main ways to create a table:

  • Datasheet View: Similar to Excel. Enter field names directly in the table grid.
  • Design View: Allows detailed field settings like data type and primary key.
  • Table Templates: Predefined tables like Contacts or Tasks.

Step 3: Define Fields

Example for a student table:

  • StudentID - Number, Primary Key
  • FirstName - Short Text
  • LastName - Short Text
  • Class - Short Text
  • DateOfBirth - Date/Time

Step 4: Set Primary Key

Right-click the field (e.g., StudentID) and select Primary Key. This ensures each record is unique.

Step 5: Save the Table

Click File > Save, give the table a name (e.g., Students), and click OK.

Step 6: Enter Data

Switch to Datasheet View and start entering student records:

  • 101, John, Doe, Form 1, 2008-05-12
  • 102, Jane, Smith, Form 2, 2007-11-23

4. Field Data Types in Tables

Choosing the correct data type ensures data is stored properly:

  • Short Text: Names, city, address.
  • Long Text: Notes, descriptions.
  • Number: Age, quantity.
  • Date/Time: Birthdates, appointment dates.
  • Currency: Money values like fees or salary.
  • Yes/No: True or False, e.g., IsActive.
  • AutoNumber: Automatically generates unique ID.

5. Practical Tips

  • Always define a Primary Key for your table.
  • Use meaningful field names.
  • Use the appropriate data type to avoid errors.
  • You can always modify tables later in Design View.
  • Tables can be linked to other tables using relationships to avoid duplicate data.

6. Example: Table Design in Design View

Field Name Data Type Description
StudentID AutoNumber Unique ID for each student (Primary Key)
FirstName Short Text Student's first name
LastName Short Text Student's last name
Class Short Text Class/Grade of student
DateOfBirth Date/Time Student's date of birth

7. Real-Life Usage Scenario

Imagine you are running a small library. You can create a table called Books with fields:

  • BookID (Primary Key)
  • Title
  • Author
  • Category
  • Quantity

Reference Book: N/A

Author name: SIR H.A.Mwala Work email: biasharaboraofficials@gmail.com
#MWALA_LEARN Powered by MwalaJS #https://mwalajs.biasharabora.com
#https://educenter.biasharabora.com

:: 1::

⬅