From this hour you will be able to learn about below topic –

How to Create Table with Datasheet View
How to Open Table
How to Delete Table
How to Rename Table
Wha is Data Type? How many Data Types available in MS Access
How to Create Table with Design View
How to Change Table Design
How to Create Table using Field Properties with Design View
How to Change Table Design
About Field Properties
How to Create Table with Table Wizard
How to Work with Table

Datasheet View: Datasheet View is used to easily create a table, but we can not set data type, field properties, and primary key at design time.

• Select Tables under Objects
• Click New on the 'database window toolbar'
• Select Datasheet View > Click Ok button
• Double Click on Field1 and Type a Field Name, like – FirstName
• Double Click on Field2 and Type a Field Name, like – LastName
• Double Click on Field3 and Type a Field Name, like – Title
• Double Click on Field4 and Type a Field Name, like – Age
• Double Click on Field5 and Type a Field Name, like – City
• Double Click on Field6 and Type a Field Name, like – Phone
• On the File menu, click Save
• Type Table Name: Employee/Author/Officer (or your choose) > Click Ok button
• [Read the message carefully] > Click Yes/No button
• Close the window

Note: If you click yes Access will create a primary key field (ID) and if you click no Access will not create primary key field. So if you want to look the difference create two tables with yes and no.

Primary key: One or more fields (columns) whose value or values uniquely identify each record in a table.

Properties of Primary key:

• A primary key cannot allow Null values and must always have a unique index.
• A primary key is used to relate a table to foreign keys in other tables.

• Select Tables under Objects
• Select the table
• Click Open on the 'database window toolbar'
• or
• Select Tables under Objects
• Double click on the table
• or
• Select Tables under Objects
• Right click on the table and click Open

• Select Tables under Objects
• Right click on the table > Click Delete > Click Yes button

• Select Tables under Objects
• Right click on the table > Click Rename > Type a new name > Press Enter

The characteristic of a field that determines what type of data it can store. For example, a field whose data type is Text can store data consisting of either text or number characters, but a Number field can store only numerical data.

Field data types available in Access (MDB)

Note:  The information in this topic applies only to a Microsoft Access database (.mdb). The following list summarizes all the field data types available in Microsoft Access, their uses, and their storage sizes.

Text : Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. Stores: up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered.

Memo: Use for lengthy text and numbers, such as notes or descriptions. Stores: up to 65,536 characters.

Number: Use for data to be included in mathematical calculations, except calculations involving money (use Currency type). Stores: 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The FieldSize property defines the specific Number type.

Date/Time: Use for dates and times. Stores: 8 bytes.

Currency: Use for currency values and to prevent rounding off during calculations. Stores: 8 bytes.

AutoNumber: Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. Stores: 4 bytes; stores 16 bytes for Replication ID (GUID).

Yes/No: Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed. Stores: 1 bit.

OLE Object: Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol. Stores: up to 1 gigabyte (limited by disk space).

Hyperlink: Use for hyperlinks. A hyperlink can be a UNC path or a URL. Stores: up to 64,000 characters.

Lookup Wizard: Use to create a field that allows you to choose a value from another table or from a list of values using a combo box— -choosing this option in the data type list starts a wizard to define this for you.
Requires the same storage size as the primary key that corresponds to the Lookup field— -typically 4 bytes.

• Select Tables under Objects
• Click New on the 'database window toolbar'
• Select Design View > Click OK button
• Field Name – SerialNo and Data Type – Autonumber
• Field Name – EmpID and Data Type – Text
• Field Name – EmpName and Data Type – Text
• Field Name – EmpTitle and Data Type – Text
• Field Name – EmpAge and Data Type – Number
• Field Name – EmpCity and Data Type – Text
• Field Name – EmpSalary and Data Type – Currency
• Field Name – EmpJoinDate and Data Type – Date/Time
• Right click on EmpID and click Primary Key
• On the File menu, click Save
• Type a unique Table Name: Employee (or your choose) > Click Ok button
• Close the 'design view' window
• Then, if you want to entry data into the Employee table, open the table and entry data. After entry data close the table.

To Create Table with Design View: (Process – 2)

• Select Tables under Objects
• Double click on the Create table in Design view from 'database window'
• Field Name – SerialNo and Data Type – Autonumber
• Field Name – EmpID and Data Type – Text
• Field Name – EmpName and Data Type – Text
• Field Name – EmpTitle and Data Type – Lookup Wizerd… (You will get a dialog box.)
  Select 'I will type in the values that I want'
  Then type some title, like below –
  > Click Next button > Click Finish button
• Field Name – EmpGender and Data Type – Text
• Field Name – EmpAge and Data Type – Number
• Field Name – EmpCity and Data Type – Lookup Wizard… (Follow previous process for city name)
• Field Name – EmpSalary and Data Type – Currency
• Field Name – EmpJoinDate and Data Type – Date/Time
• Field Name – EmpCV and Data Type – Hyperlink
• Field Name – EmpNote and Data Type – Memo
• Field Name – EmpPhoto and Data Type – OLE Object
• Right click on EmpID and click Primary Key
• Save the table with a unique name, as – NewEmployee (Note: If you type same/previous table name and at saving time click 'Yes', you will lose your previous table.)
• Close the 'design view' window
• Then, if you want to entry data into the NewEmployee table, open the table and enter data. After entry data close the table.

• Select Tables under Objects
• Select the table NewEmployee (or your choose)
• Click Design on the 'database window toolbar'
• Then Change the design (field name or data type)
• On the File menu, click Save
• Close the 'design view' window

• Select Tables under Objects
• Double click on the Create table in Design view
• Field Name – SerialNo, Data Type – AutoNumber and Field Properties – Format: General Number, Indexed: Yes (No Duplicates)
  
• Field Name – EmpID, Data Type – Text and Field Properties – Field Size: 15, Caption: EmployeeID
• Field Name – EmpName, Data Type – Text and Field Properties – Field Size: 25, Format: >, Required: Yes, Allow Zero Length: No, Indexed: Yes (Duplicates OK)
• Field Name – EmpTitle, Data Type – Lookup Wizerd… (You will get a dialog box.)Select 'I will type in the values that I want'
  Then type some title, like below –
   > Click Next button > Click Finish button and Field Properties – Field Size: 20, Default Value: Officer, Required: Yes, Allow Zero Length: No, Indexed: Yes (Duplicates OK)
• Type Field Name – EmpGender, Data Type – Text and Field Properties: Field Size – 1, Format: >, Default Value: "M", Validation Rule: "M" Or "F", Validation Text: Please type M for male and F for female., Required: Yes, Indexed: Yes (Duplicate Ok)
  
  
• Field Name – EmpAge, Data Type – Number and Field Properties – Field Size: Byte, Format: General Number, Indexed: Yes (Duplicates OK)
• Field Name – EmpCity, Data Type – Lookup Wizard… (Follow previous process for city name.) and and Field Properties – Field Size: 20, Default Value: Mymensingh, Required: Yes, Indexed: Yes (Duplicates OK)
• Field Name – EmpSalary, Data Type – Currency and Field Properties – Indexed: Yes (Duplicates OK)
• Field Name – EmpJoinDate, Data Type – Date/Time and Field Properties – Format: Short Date, Input Mask: Click Toggle button > Yes > Select Short Date & Next> Next > Finish
• Field Name – EmpPhone, Data Type – Text and Field Properties – Field Size:13, Format: @@-@@@@@-@@@@@@, Required: Yes, Indexed: Yes (No Duplicates)
  
• Right Click on EmpID and click Primary Key
• Save the table with a unique name, as –NewEmployeeFP (Note: If you type same/previous table name and at saving time click 'Yes', you will lose your previous table.)
• Close the 'design view' window
• Then, if you want to entry data into the NewEmployee table, open the table and enter data. After entry data close the table.

• Select Tables under Objects
• Select the table NewEmployee (or your choose)
• Click Design on the 'database window toolbar'
• Then Change the design (field name or data type)
• On the File menu, click Save
• Close the 'design view' window

FieldSize Property: We can use the FieldSize property to set the maximum size for data stored in a field set to the Text, Number, or AutoNumber data type. We should use the smallest possible FieldSize property setting because smaller data sizes can be processed faster and require less memory.

Setting

• If the DataType property is set to Text, enter a number from 0 to 255. The default setting is 50.
• If the DataType property is set to AutoNumber, the FieldSize property can be set to Long Integer or Replication ID.
• If the DataType property is set to Number, the FieldSize property settings and their values are related in the following way.

Format Property: We can use the Format property to customize the way numbers, dates, times, and text are displayed and printed. You can use one of the predefined formats or you can create a custom format by using formatting symbols. The Format property uses different settings for different data types. The Format property affects only how data is displayed. It doesn't affect how data is stored. For information about settings for a specific data type, see one of the following topics:

Format Property - Date/Time Data Type

The following table shows the predefined Format property settings for the Date/Time data type.

Format Property - Number and Currency Data Types

The following table shows the predefined Format property settings for numbers.

Format Property - Text and Memo Data Types

You can create custom text and memo formats by using the following symbols.

The following are examples of text and memo custom formats.

Note: Microsoft Access provides predefined formats for Date/Time, Number and Currency, Text and Memo, and Yes/No data types. The predefined formats depend on the country/region specified by double-clicking Regional Options in Windows Control Panel. Microsoft Access displays formats appropriate for the country/region selected. For example, with English (United States) selected on the General tab, 1234.56 in the Currency format appears as $1,234.56, but when English (British) is selected on the General tab, the number appears as £1,234.56.
If you set a field's Format property in table Design view, Microsoft Access uses that format to display data in datasheets. It also applies the field's Format property to new controls on forms and reports.

InputMask Property: We can use the InputMask property to make data entry easier and to control the values users can enter in a text box control. Input masks are helpful for data-entry operations such as an input mask for a Phone Number field that shows you exactly how to enter a new number: (___) ___-____.

Caption Property: We can use the Caption property to provide helpful information to the user through captions on objects in various views:

• Field captions specify the text for labels attached to controls created by dragging a field from the field list and serves as the column heading for the field in table or query Datasheet view.
• Form captions specify the text that appears in the title bar in Form view.
• Report captions specify the title of the report in Print Preview.
• Button and label captions specify the text that appears in the control.

DefaultValue Property: We can use DefaultValue property to specify a string value that is automatically entered in a field when a new record is created. For example, in an Addresses table you can set the default value for the City field to Mymensingh. When users add a record to the table, they can either accept this value or enter the name of a different city.

ValidationRule Property: We can use the ValidationRule property to specify requirements for data entered into a record, field, or control. When data is entered that violates the ValidationRule setting.

ValidationText Property: Use the ValidationText property to specify a message to be displayed to the user when data is entered that violates a ValidationRule setting for a record, field, or control.

Required Property: We can use the Required property to specify whether a value is required in a field. If this property is set to Yes, when you enter data in a record, you must enter a value in the field or in any control bound to the field, and the value cannot be Null. For example, you might want to be sure that a LastName control has a value for each record. When you want to permit Null values in a field, you must not only set the Required property to No but, if there is a ValidationRule property setting, it must also explicitly state "validationrule Or Is Null".

AllowZeroLength Property: We can use the AllowZeroLength property to specify whether a zero-length string (" ") is a valid entry in a table field.
Note  The AllowZeroLength property applies only to Text, Memo, and Hyperlink table fields.
The AllowZeroLength property uses the following settings.

Indexed Property: We can use the Indexed property to set a single-field index. An index speeds up queries on the indexed fields as well as sorting and grouping operations. For example, if you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name.

The Indexed property uses the following settings.

Weak Entity/Table: An entity set may not have sufficient attributes/fields to from a primary key. Such an entity set is termed as weak set.

Strong Entity/Table: An entity set that has a primary key id termed as strong entity set.

• Select Tables under Objects
• Click New on the 'database window toolbar'
• Select Table Wizard > Click Ok button
• Choose a table category, Business or Personal > Choose a table, as – Products from Sample Tables: list > Add fields from Sample Fields: list to Fields in my new table: list > Click Next button and you will get below wizard–
• Type a table name and choose 'No, I'll set the primary key.'
  [Note: If you choose 'Yes, set a primary key for me.' wizard will set primary key field for the table with AutoNumber data type and if you choose 'No', I'll set the primary key.' You'll able to set the primary key field for the table.]
• Click Next button and you will get below wizard –
• What field will hold data that is unique for each record? ProductID is selected or you can choose another one but don’t need because ProductID is appropriate.
• What type of data do you want the primary key field to contain? First option mention primary key field's data type will AutoNumber, second option mention primary key field's data type will Number, and third option mention primary key field's data type will Text.
  So select as you choose.
  [Note: use all 3 options for practice purpose.]
• Click Next button and you will get below wizard –
  [Note: This wizard help you for relation to any other table, but in this case you have to follow the rules of relation.]
• Click Next button and you will get below wizard–
  After the wizard creates the table, what do you want to do? First option mention you will able to change table design (field name, data type, description, and field properties), second option mention you'll able enter data into the table, and third option mention you will able to create form to enter data into the table.
  [Note: use all 3 options for practice purpose.]
• Select 'Enter data directly into the field.' and click Finish

• Open a table (employee or your choose)
• Which record you want to select, set cursor it's any cell
• On the Edit menu, click Select Record

To Delete Record:

• Open a table (employee or your choose)
• Which record you want to delete, set cursor it's any cell
• On the Edit menu, click Delete Record
• Click Yes button

To Select All Records:

• Open a table (employee or your choose)
• Set cursor into any cell
• On the Edit menu, click Select All Records

To Delete Column:

• Open a table (employee or your choose)
• Which column you want to delete, set cursor it's any cell
• On the Edit menu, click Delete Column
• Click Yes button

To Find Data: (Process 1)

• Open a table (employee or your choose)
• Which column's data you want to find, set cursor it's any cell
• On the Edit menu, click Find
• Type data/value into 'Find What:' box > Click Find Next button

To Find Data: (Process 2)

• Open a table (employee or your choose)
• On the Edit menu, click Find
• Type data/value into 'Find What:' box > Set Look In: Employee: Table or your choose > Set Match: Any Part of Field/Whole Field/Start of Field what you want > Select Match Case if you want > Click Find Next button

To Replace Data: (Process 1)

• Open a table (employee or your choose)
• Which column's data you want to replace, set cursor it's any cell
• On the Edit menu, click Replace
• Type old data/value into 'Find What:' box > Type new data/value into 'Replace With:' box > Click Find Next button > Click Replace/Replace All button

To Replace Data: (Process 2)

• Open a table (employee or your choose)
• On the Edit menu, click Replace
• Type old data/value into 'Find What:' box > Type new data/value into 'Replace With:' box > Set Look In: Employee: Table or your choose > Set Match: Any Part of Field/Whole Field/Start of Field what you want > Select Match Case if you want > Click Find Next button > Click Replace/Replace All button

To Format Font:

• Open a table (employee or your choose)
• On the Format menu, click Font…
• Change Font, Font style, Size, or Color what you want > Click Ok button

To Format Datasheet:

• Open a table (employee or your choose)
• On the Format menu, click Datasheet…
• Change Cell Effect, Background Color, etc. as you choose > Click Ok button

To Format Row Height:

• Open a table (employee or your choose)
• On the Format menu, click Row Height…
• Change Row Height: value as you choose > Click Ok button

To Format Column Width:

• Open a table (employee or your choose)
• On the Format menu, click Column Width…
• Change Column Width: value as you choose > Click Ok button

To Rename Column:

• Open a table (employee or your choose)
• Select column which you want to rename
• On the Format menu, click Rename Column
• Type a new name > Press Enter

To Hide Column:

• Open a table (employee or your choose)
• Select column which you want to hide
• On the Format menu, click Hide Columns

To Unhide Columns:

• Open a table (employee or your choose)
• On the Format menu, click Unhide Columns…
• Select Column > Click Ok button

To Freeze Column:

• Open a table (employee or your choose)
• Select column which you want to freeze
• On the Format menu, click Freeze Columns

To Unfreeze Column:

• Open a table (employee or your choose)
• Select column which you want to unfreeze
• On the Format menu, click Unfreeze Columns

To Filter Records:

• Open a table (employee or your choose)
• Which record you want to filter, set cursor it's any cell, as – EmpCity column which is contain Dhaka or your choose
• On the Records menu, point to Filter and click Filter by Selection

To Remove Filter:

• On the Records menu,
• Click Remove Filter/Sort

To Sort Records as Ascending Order:

• Open a table (employee or your choose)
• Which record you want to sort, set cursor it's any cell
• On the Records menu, point to Sort and click Sort Ascending

To Sort Records as Descending Order:

• Open a table (employee or your choose)
• Which record you want to sort, set cursor it's any cell
• On the Records menu, point to Sort and click Sort Descending