Access labs 2017

Lesson

2

Lesson 2 - Working with Tables

Lesson Topics:

2.1 Creating a Table using Application Parts

2.2 Entering Data into a Table

2.3 Adding New Fields to a Table

2.4 Creating a Table from Scratch

2.5 Setting a Primary Key

2.6 Changing Column Width and Row Height

2.7 Rearranging Fields

2.8 Inserting and Deleting Fields

2.9 Changing Field Properties

2.10 Designing in Datasheet View

2.1 Creating a Table using Application Parts

In this lesson, you will learn how to create a new table using Application Parts.

A

pplication Parts allow you to build your database using pre-designed components. Introduced in Access 2010, Application Parts contain several pre-built tables that are commonly used in databases to get you started: Contacts, Issues, Tasks and Users. XE "Templates:table" XE "Application Parts:using" XE "Tables: creating using Application Parts"

If the tables you need to create are different than the available pre-designed tables, you may want to consider simply creating your table from scratch and save the time you would spend changing field names and data types. You may also wish to explore the collection of professionally designed database templates, which contain a usable set of data tables.

To Create a Table using Application Parts

1. Click the Create tab on the Ribbon.

2. Click the Application Parts button on the Templates group on the Ribbon to display a list of available Application Parts.

3. Click on the Application Part in the list that you wish to use. Database items (tables, forms, reports, queries) associated with the application part will then be created.

Let’s Try It!

What	Why
1. With Access open, click the File tab.	Displays the File Options menu.
2. Click Open.	Displays the Open dialog box.
3. Click the Browse icon in the right pane.	Displays the Open window.
4. Click Desktop on the left side of your screen.	Opens the Desktop folder.
5. Double-click the Lesson Files folder on the right side of your screen.	Opens the Lesson Files folder and displays the files in that folder.
6. Select the Video Sales 2 file and then click Open.	Opens the Video Sales 2 database.
7. Click the Create tab on the Ribbon as shown below.	Displays commands related to creating objects in Access.
image2
8. Click the Application Parts icon on the Templates group of the Ribbon.	Displays a list of available Application Parts.
9. Click on Contacts in the Application Parts list as shown below.	Creates a new table using the Contacts template and opens it in Datasheet view.
image3
10. Click Cancel when asked if you wish to create a relationship.	Closes the relationship dialog box.
11. Double-click the Contacts table in the Navigation pane.	Opens the new table that we just created.
12. Click the Access Objects button in the navigation pane as shown below and select “All Access Objects”	Displays all Access Objects. Notice that the Application Part also created associated queries, forms and reports. As we do not need them, we will delete them from the database.
image4
13. Under the Queries area, click the object named ContactsExtended, hold down the Shift key on your keyboard and then click Label under the Reports category in the Navigation pane so that all of the query, form and report objects are selected as shown.	Selects all of the objects between ContactsExtended and Label. image5
14. Press the Delete key on your keyboard. Click Yes to confirm the deletion.	Deletes the selected objects.
15. Click the Close button on the table window.	Closes the table.

2.2 Entering Data into a Table

In this lesson, you will learn how to enter table data.

T

o enter data into a new table, click in the first field into which you wish to begin entering data and then type your information. Pressing the Tab key will move you to the next field. Note that you cannot enter data into an AutoNumber field.

To enter data into a table with existing data, click the New Record button on the Record Navigation Bar and then type your information into the new row. XE "Tables:entering data into" XE "Data:entering into tables"

To Enter Data into a Table

1. Open the table in Datasheet view.

2. Click the New Record button on the Record Navigation Bar to insert a new record. XE "New Record Button"

3. Click in the first blank field and begin typing.

Let’s Try It!

What	Why
1. Select tblOrders in the Navigation Pane and then press Enter.	Opens tblOrders in Datasheet view. You can also double-click a table to open it in Datasheet view.
2. Click in the Customer ID field and then type: 32	Enters the CustomerID number.
3. Press Tab.	Moves to the next column.
4. Type: 02/02/15 in the Order Date field and then press Tab.	Enters the Order Date and then moves to the next column.
5. Type: UPS in the Shipper field and then press Tab.	Enters the Shipper and then moves to the next field.
6. Type: 02/07/2015 in the Ship Date field and then press Tab.	Enter the Ship Date and then moves to the next field.
7. Type: 5.95 and then press Tab.	Enters the Freight Charge and then moves to a new record. Note that you do not have to “save” your data – when you move off the row, the data is automatically saved.
image7 The Completed Record

2.3 Adding New Fields to a Table

In this lesson, you will learn how to add new fields to a table.

W

hen you need to add new fields to your table, delete existing fields, or modify the formatting properties of your table, you will often want to work in Design view. In fact, as you become more comfortable working with Access, you may find yourself creating the majority of your tables in Design view, rather than using database templates. Using Design view allows you to add fields, select a data type and enter a description for your fields. Design View contains many powerful features and allows for maximum flexibility. XE "Tables:Design view" XE "Design View:tables"

In Design view, each field contains three properties:

· Field Name – the name of your field

· Data Type – allows you to specify what type of data can be entered into the field

· Description– allows you to add optional notes to describe the field in more detail

Table Design View

You also have the ability to add new fields while in Datasheet view. To add a new field in Datasheet view, click on the column heading that reads Click to Add and chose the data type of the field from the list. Then, type in the name for the field.

Rather than choosing a data type, you can also simply click in the first blank cell under the Click to Add heading and begin entering data in the new field. When you enter data in the new column, Access 2016 uses the information you type to recognize the appropriate data type. For instance, if you type: 5/17/2015, Access should recognize it as a date and set the data type for the field to Date/Time. If Access is unable to guess the data type, the default data type is set to Short Text.

To Switch to Design View

1. From the Home tab, select Tables in the Navigation Pane to display database tables.

2. Double-click the table to open it in Datasheet view.

3. Click the View command button on the Ribbon to toggle between Design View and Datasheet View. XE "Design View Button"

Or

1. Right-click on the table and choose Design View from the contextual menu.

Or

Click the Design View button on the lower right-hand corner of your screen

`

To Add a New Field from Datasheet View

1. Display Table objects in the Navigation Pane.

2. Double-click the table to open it in Datasheet view.

3. Click on the column heading that reads Click to Add and then choose the data type for the field from the list.

4. Type in the name for your new field and then press Enter.

Let’s Try It!

What	Why
1. Click on the column heading that reads Click to Add as shown below.	Displays a list of available data types.
image11
2. Click Date & Time from the list as shown below.	Sets the data type to Date & Time and then highlights the title of the fields so that you can provide it a meaningful field name.
image12
3. Type: DateArrived and press Enter.	Creates the field and then moves the end of the table, allowing you to create another new field.
4. Click the Design View button on the lower right-hand corner of the Access screen.	Switches to Design view.
5. Highlight the ShipDate field as shown below.	Selects the ShipDate field. We are going to change the field name of this field.
image13
6. Type: DateShipped	Renames the ShipDate field to DateShipped.
7. Click the Save button on the Quick Access toolbar.	Saves the design changes to the table. Though you do not need to save changes to your data, you do need to save your design changes.
8. Click the table’s close button.	Closes the table.

2.4 Creating a Table from Scratch

In this lesson, we will create a new table in Design view.

A

s you work more with Access, you will most likely want to create your tables from scratch rather than using templates. Creating your tables from scratch allows you maximum flexibility.

When adding fields to a table, you’ll need to specify a field name and a data type. Data types tell Access what type of data to expect in that field. For example, you would be unable to calculate two numbers if their data type was set to Text. Thus, it’s important to have a general understanding of data types. XE "Tables:creating from scratch" XE "Data Type:defined"

Data Type	Description
Short Text	Stores text and/or numbers. Set for any field that will not be used for calculation. Entries can be up to 255 characters.
Long Text	Allows you to store up to 1 gigabyte of data in a field in desktop databases.
Number	Stores numbers that will be used in calculations. For numbers beginning with 0 (such as employee numbers for instance), use the text data type as the 0 will be dropped if data type is set for number.
Date/Time	Used for date and/or times.
Currency	Stores numbers with a fixed number of decimal places and a currency symbol.
AutoNumber	Sequentially numbers the records. AutoNumber is generally used for primary key fields for which unique values are required.
Yes/No	A Boolean value that represents a yes/no or true/false value.
OLE Object	Used for objects created in other applications such as Excel spreadsheets, graphics, and sound files.
Hyperlink	Used for clickable links to files on your computer or to Web sites on the Internet.
Attachment	Allows you to attach images, spreadsheet files, documents, and other supported files to records in your database. More flexible than OLE fields.
Calculated	Allows you to store a calculated value in a field.
Lookup Wizard	Used to create a field that allows you to select a value from a list or from a field in another table or query.

To Create a Table from Scratch

1. Click the Create command tab on the Ribbon.

2. Click the Table command icon.

3. Click the View button to switch to Design view.

4. Enter a Field Name and Data Type for each field.

5. Type in a description for each field if desired.

Let’s Try It!

What	Why
1. Click the Create command tab on the Ribbon.	Displays commands related to the creation of database objects.
2. Click Table command icon on the Tables group of the Ribbon.	Creates a new table and opens it in Datasheet view.
3. Click the Home tab on the Ribbon.	Switches to Home commands and tools.
4. Click the View command icon on the Ribbon.	Switches to Design view.
5. Type: tblShippers in the Table Name box.	Before we can switch to Design view, we must first provide a name our new table.
6. Click OK.	Saves the table and switches to Design view. Notice that Access automatically created our first field for us – the ID field.
7. Click on the key next to the ID field as shown below and then press the Delete key. Click Yes when asked if you want to delete the field.	Deletes the default field that Access provided. We are going to create our own ID field.
image14
8. Click in the first blank Field Name box and type: ShipperID as shown below.	Enters a name for the first field.
image15
9. Press Tab.	Moves our cursor to the Data Type Column. A default value of “Short Text” is displayed for the data type. As the Shipper ID field will be used for a primary key field, we will want to change the data type to an AutoNumber field.
10. Click on the Data Type arrow and then select AutoNumber from the list, as shown below.	Sets the data type for the ShipperID field to AutoNumber.
image16
11. Press Tab to jump to the Description field and then type: This is the Shipper ID	Enters a description for the first field.
12. Press Tab.	Moves to the next blank Field Name box.
13. Type: ShipperName	Enters the field name for the next field.
14. Press Tab.	We will accept the default Short Text data type.
15. Press Tab and then type: This is the Shipper Name.	Enters a description for the second field.
16. Press Tab and then type: ShipperPhone.	Moves to the next blank Field Name box and enters “ShipperPhone” as the next field name.
17. Press Tab twice.	Accepts the default Short Text data type and moves to the description field. As a telephone number contains non-numerical characters such as dashes and parenthesis, we must set the data type as short text, and not number.
18. Type: This is the Shipper Phone Number.	Enters a description for the third field.
19. Click the Save icon on the Quick Access Toolbar.	Saves our changes.

A Note about Naming Conventions

Most database developers use some sort of naming conventions to help them organize and quickly identify the database objects in their database. A popular naming convention uses the object type, in lowercase letters, as the first 3 letters of the object name. For example, a Customer’s table would be named: tblCustomers, a customer’s form would be named frmCustomers, a customer’s query would be name qryCustomers and a Customer’s report would be named rptCustomers .

Naming conventions are optional. However, it is considered good database programming practice to use some sort of naming convention for your objects.

2.5 Setting a Primary Key

In this lesson, you will learn about Primary Keys.

W

hen working with a relational database such as Microsoft Access, you will often need to link two or more tables to bring related information together. In order to do this, each table needs to include a field that uniquely identifies each record in the table. This means that the data in that field may not be duplicated in any other of the table records. This unique identifier field is called a Primary Key. XE "Primary Key:setting" XE "Primary Key:rules for setting"

A primary key is typically a number, such as an order number, customer number, invoice number or social security number. The most important consideration when adding a primary key to a table is that it must be unique. Some additional rules to keep in mind when setting a primary key field:

· The primary key field can never be blank (or null)

· The primary key field can never be duplicated

· The primary key field should be as short as possible

· The primary key should describe the entity

Another purpose of the primary key is that it indexes the information in the record. Indexing helps Access find information quickly, especially when you have a large amount of data.

Designating an AutoNumber as the primary key for a table is often the easiest way to create a unique identifier. If you neglect to set a primary key, Microsoft Access will ask if you want it to create a primary key for you. If you answer yes, Access will create an AutoNumber primary key. As we saw in a previous lesson, Access automatically creates an ID field with a primary key for all new tables. XE "Primary Key Icon"

To Add a Primary Key field to a table

1. Click in the Field Name box of the field you wish to set as a primary key field.

2. Click the Primary Key icon on the Ribbon.

Or

Right-click and then select Primary Key from the pop-up menu.

Let’s Try It!

What	Why
1. Click in the Field name box (the gray box to the left of the field name) for ShipperID	Selects the field that we want to set as a primary key field. You can also simply click anywhere in the file name box to select the field.
2. Click the Primary Key command icon on the contextual Design tab of the Ribbon.	Sets the ShipperID field as a key field. The key symbol to the left of the ShippperID field informs us that this field is set as a primary key field. image18.wmf Primary Key indicator
3. Click the Save button on the Quick Access Toolbar.	Saves our changes.

2.6 Changing Column Width and Row Height

In this lesson, you will learn how to change the width of columns and the height of rows in a table.

A

fter creating your tables, you may discover that your columns are not wide enough to accommodate your data. Luckily, you can effortlessly change the width of your table columns. If you have worked with Microsoft Excel or other similar spreadsheet applications, you may already be familiar with the process of changing column width as it works the same way in an Access table. The process for changing the height of rows is the same as changing the width of columns. XE "Table:chaning column width of" XE "Table:changing row height of" XE "Columns:changing width of in tables" XE "Rows:changing height of in tables"

To Change the Width of a Column or the Height of a Row

1. Move your mouse pointer over the boundary of the right side of the column heading until the mouse pointer changes into a black cross with a horizontal double arrow pointer. To change a row height, drag the boundary of the bottom side of the row heading until the mouse pointer changes into a black cross with a vertical double arrow pointer.

2. Click and hold down the left mouse button, and then drag until the column is the desired width or the row is the desired height.

Move cursor over boundary until it

turns into a horizontal double

-

arrow

point and then click and drag

AutoFit

You can also use the AutoFit feature. This allows you to automatically change the width of a column to accommodate the widest entry. XE "AutoFit:using"

To Change the Size of a Column using AutoFit:

1. Double-click on the right border of the column heading. The column width will adjust to accommodate the largest entry in that column.

Double

-

click on

border to adjust

column width

automatically

Let’s Try It!

What	Why
1. Click the View command icon on the Ribbon.	Switches to Datasheet view.
2. Move your mouse pointer over the border between ShipperName and ShipperPhone until the pointer transforms into a black cross with a horizontal double arrow pointer.	Enters drag mode.
3. Click and drag to the right until the column is about 2 inches wide as shown below.	Increases the width of the ShipperName column to about 2 inches.
image21.wmf Drag until column is about 2 - inches wide

2.7 Rearranging Fields

In this lesson, you will learn how to change the position of fields.

A

fter creating your tables, you may decide that some of the fields are not in the order that you would like. To move a field from one location to another, use the click-and-drag method. That is to say, click the record selector for the field you wish to move, and then drag it to the new location. As you drag, a dark line appears, indicating the location of the field should you release the mouse button. XE "Fields:rearranging in tables"

To Rearrange Fields in a Table

1. Open the table in Design view.

2. Click the record selector to the left of the field you wish to move to select the record.

3. Click on the selected record and then drag the field to its new location (as you drag, a dark line appears, indicating the location of the field).

Let’s Try It!

What	Why
1. Click the Close Button for tblShippers. Save any changes.	Closes the tlbShippers table.
2. Right-click on tblOrders and then choose Design View from the contextual menu.	Opens tblOrders in Design view.
3. Click the record selector to the left of the DateShipped field.	Selects the DateShipped field.
4. Move your mouse pointer over the record selector for DateShipped, and then click and drag upwards until the DateShipped field is after the OrderDate field as shown below.	Repositions the DateShipped field after the OrderDate field
image23
5. Release the mouse button.	Completes the move process.
6. Click the Save button.	Saves the design changes.

2.8 Inserting and Deleting Fields

In this lesson, you will insert new fields into and delete existing fields from a table.

Y

ou can insert new fields anywhere in your table in Design view by selecting the field above which you wish to insert a new field and then clicking the Insert Rows command button on the Ribbon. You can also right-click the row above which you want to insert a new row and select Insert Rows from the contextual menu. XE "Fields:inserting in tables" XE "Fields:deleting in tables"

To delete a field, select the field and then press the Delete key or click the Delete Rows command button on the Ribbon. But keep in mind that when you delete a field, all the data in the field is deleted along with it!

To Insert a New Field in a Table

1. Display the table in Design view.

2. Click in the Field Name box of the field above which you wish to insert a new field.

3. Click the Insert Rows command button on the Ribbon

Or

Right-click on the field above which you want to insert a new row and select Insert Rows from the contextual menu.

To Delete a Field from a Table

1. Display the table in Design view.

2. Click the record selector of the field you wish to delete.

3. Click the Delete Rows command button on the Ribbon

Or

Press the Delete key.

4. Click Yes if asked if you want to permanently delete the selected field along with its data.

Let’s Try It!

What	Why
1. Click the record selector to the left of the FreightCharge field.	Selects the Freight Charge field. We wish to insert a new field above this field.
2. Click the Insert Rows command button on the Ribbon.	Inserts a new field above the Freight Charge field.
3. Click in the Field Name box of the new field and then type: TaxRate	Enters a name for our new field.
4. Press Tab.	Moves to the Data Type column. We will accept the default value for the moment.
5. Press Tab again then type: This is the customer’s tax rate as shown below.	Enters a description for the new field.
image25
6. Click the Save button.	Saves the design changes.
7. Click the table’s Close button.	Closes tblOrders.
8. Select tblShippers, right-click and then choose Design View from the menu.	Opens tblShippers in Design view.
9. Click the record selector to the left of ShipperPhone.	Selects the ShipperPhone field.
10. Press the Delete key.	Deletes the selected record. As there is not yet any data in our table, Access does not ask us if we want to permanently delete the selected field along with its data.
11. Click the Save button.	Saves our design changes.

2.9 Changing Field Properties

In this lesson, you will work with the Field Properties of a field.

W

hile data types tell Access what type of data the field will hold, field properties govern how the data is displayed or stored. Each data type has its own set of field properties. For example, you can set the field size of the text data type to only allow 3 digits whereas the field size of the number data type is dependent on the size and type of number you choose (integer, long integer, single, double, decimal, etc.). XE "Field Properties:changing"

In this lesson, we will look at a couple of the more common field properties. The table on the next page summarizes some of the field properties that are most frequently modified.

To Modify Field Properties

1. Display the table in Design view.

2. Click in the field name box of the field whose field properties you wish to change.

3. Click the General pane in the field properties window.

4. Make any desired changes.

Common Field Properties XE "Field Properties:types of"

Field Property	Description
Field Size	Tells Access the maximum number of characters that can be stored in the field. For text, this is a number up to 255 characters. For numbers, this is a number type (long integer, double, decimal, integer, etc.)
Input Mask	A string of characters on the screen representing how data is to be entered by the user.
Format	How data entered is to be displayed. You can use a pre-defined format or use a custom format.
Decimal Places	The number of decimal places that will be displayed.
Caption	The text (or label) that should appear next to the text box control on a form. If no caption is entered, Access uses the field name.
Default Value	The value that Access automatically enters in the field for new records.
Validation Rule	An expression that controls the value that can be entered into a field.
Validation Text	The message the user receives when the validation rule is violated.
Required	A yes/no property that specifies whether a user must enter a value in the field.
Allow Zero Length	Specifies whether a string containing no characters (a zero-length string) is permissible. You enter a zero-length string by typing two quotation marks with no spaces between them (“”).
Indexed	Specifies whether you want to Access to create a data index for the field that can speed up searches and sorts.

Let’s Try It!

What	Why
1. Click the Close button for tblShippers.	Closes the tblShippers table.
2. Right-click tblOrders and then choose Design View button from the contextual menu.	Displays tblOrders in Design view.
3. Click in the Data Type box for the TaxRate field.	Selects the TaxRate field and displays the drop-down data type arrow.
4. Click the drop-down arrow in the data type box and select Number as shown.	Changes the data type for the TaxRate field to number.
image27
5. Under the Field Properties area on the bottom of the window, click in the Field Size box as shown below.	Selects the Field Size property for the TaxRate field.
image28
6. Click the Field Size arrow and then select Decimal.	Selects decimal as the number field size (a decimal field size will reserve 12 bytes of space).
7. Click in the box next to Format.	Selects the Format property for TaxRate.
8. Click the drop-down arrow and then select Percent from the list as shown below.	Sets Percent as the number format.
image29
9. Double-click in the Scale field and then type: 3.	Sets the maximum number of places to the right of the decimal to 3.
10. Double-click in the Default Value box and then type: .055	Enters a default value of .055 or 5.5%
11. Click in the Field Name box for the Shipper field.	Selects the Shipper field and then displays the field properties for a text field.
12. Under the Field Properties area, double-click in the Field Size box and then type: 25.	Sets the maximum field size to 25 characters.
13. Click the View button on the Ribbon. Click Yes when asked if you want to save the table. Click Yes when the “Data may be lost” information box appears as shown below.	Saves the design changes and switches to datasheet view. Whenever you change the field size of a field to a smaller field size, the “Data may be lost” box will appear. If we had a Shipper name in our table that was more than 25 characters, the data would be truncated to match our new field size.
image30
14. Click in the Tax Rate field for the first record and type: .06. Press Tab.	Enters the tax rate for the first record. Notice that a tax rate of 5.5% is automatically inserted for new records.
image31

2.10 Designing in Datasheet View

In this lesson, you will work with some of the new Design options in Datasheet View.

I

f you have worked with older versions of Access, you have no doubt done all of your design work in Design View. However, with the new user interface redesign, including many easy access Datasheet view design tools, you may find yourself doing much of your design work in Datasheet view.

When you need to add new fields to your table, you can do so directly from Datasheet view using the contextual Fields tab under Table Tools on the Ribbon. From the Fields Ribbon, you can add fields, select a data type (specify what kind of information can be entered), enter a description for your fields and much more. XE "Tables:adding fields to in Datasheet View" XE "Fields tab:using" XE "Table Tools:using features of" XE "Quick Start:using"

The contextual Fields tab

To quickly add a field to a table, click in the field at the location where you wish to insert a new field (the new field will be inserted to the right of the active field) and then click the field type you wish to add under the Add & Delete area of the Ribbon. For instance, you would choose Text for a customer name field or Currency for the price of an item.

In addition to inserting fields, you can specify Data Type and Data Format, delete and rename existing fields, set the Unique and Is Required property for fields and insert Quick Starts (pre-created sets of fields allowing you to speed up the table design process). With these features right at your fingertips, you can spend less time switching back and forth from Design view to Datasheet view. XE "Datasheet view:designing in" XE "Datasheet Tab" XE "Field Templates: adding to tables"

To Design from Datasheet View

1. Display the table in Datasheet view from the Navigation Pane.

2. Click the contextual Fields tab on the Ribbon (under the contextual Tables Tools tab set).

3. To change the Data Type of a field, select the field, click the Data Type drop-down arrow on the Formatting group of the Ribbon, and choose the desired new Data Type. Chose any desired formatting from the Format drop-down list.

4. To insert a new field within your table, select the row to the right of which you want to insert a new row and click the command button for the desired Data Type under the Add & Delete group.

5. To add fields with additional data types, click the More Fields button and choose the desired data type from the list.

6. To delete a field, click anywhere in the field column you want to delete and click the Delete button on the Add & Delete group of the Ribbon.

7. To rename a field, click anywhere in the field column you want to rename, click the Name & Caption button on the Properties group of the Ribbon and type in the new name. If desired, enter a caption and description for the field as well.

8. To insert a Quick Start (a pre-created set of fields), click the More Fields button and then choose the Quick Start set you wish to insert under the Quick Start area.

Let’s Try It!

What	Why
1. Click the Fields tab on the Ribbon (under Table Tools).	Displays the contextual Fields Ribbon..
2. Click in the DateArrived column and click the Delete command button on the Ribbon. Click Yes when asked if you want to delete the field.	Deletes the DateArrived field from the database.
image33
3. Click in the DateShipped column and click the Date & Time command button on the Add & Delete group on the Ribbon.	Inserts a new field to the right of the DateShipped column.
4. Type: DatePromised. Press Enter when finished.	Provides a name for our new field and confirms the entry.
5. Click in the FreightCharge field.	Selects the FreightCharge field.
6. Click the More Fields command button on the Ribbon and choose PaymentType from the Quick Start area as shown below.	Inserts the Payment Type field to the left of the Freight Charge field.
image34
7. Click the More Fields command button on the Ribbon and choose Long Text from the Basic Types area.	Add a new Long Text data type field to the table.
8. Type Notes and then press Enter.	Names the new field “Notes”.
9. Click the File tab button on the Ribbon and click Close from the File Options menu. Click Yes if asked to save the table layout.	Saves the changes to our table and closes the database.

Lesson Summary – Working with Tables

· In this lesson, you learned how to create tables using Application Parts. Application Parts allow you to build your database using pre-designed components.

· Next, you learned how to enter data into a table in Datasheet view.

· Then, you learned how to enter fields in a table both in Datasheet view and in Design view. You learned how to switch views by clicking on the View button on the Ribbon.

· Then, you learned how to create a table from scratch by clicking the Table command button under the Create tab. You also learned about the different data types that you can set for fields. You also learned how to name your database objects according to naming conventions.

· Then, you learned about primary keys and how to designate a field with a primary key by clicking on the Primary Key icon on the Ribbon.

· Then, you learned how to change the column width and row height of tables by clicking and dragging. You also learned how to automatically adjust the width of a column to accommodate the widest entry by double-clicking on the right border of the column heading.

· Then, you learned how to rearrange fields in Design view by clicking on the record selector and dragging the field to its new location.

· Then, you learned how to insert and delete rows from a table by clicking on the Insert Rows or Delete Rows command button on the Ribbon.

· Then, you learned about field properties and how to set them for your fields in Design view. You also learned about some of the common field properties that are used in databases.

· Lastly, you learned about the Datasheet design tools available under the contextual Fields tab of the Ribbon. You learned how to insert and delete fields, add new fields based on field templates and rename fields while in Datasheet view.

Lesson 2 Quiz

1. Which is not a pre-built table type available under Application Parts?

A. Assets

B. Tasks

C. Contacts

D. Issues

2. Under what Command Tab is the Application Parts command button located?

A. Database Tools

B. Tables

C. Create

D. Templates

3. In what table view do you enter data?

A. Data View

B. Design View

C. Data Entry View

D. Datasheet View

4. In Design view, what are the three properties that each field contains?

A. Field Name, Data Type and Description

B. Field Name, Format and Data Type

C. Field Name, Field Size and Format

D. Field Format, Data Type and Default Value

5. You can only set the data type for a field in Design view.

A. True

B. False

6. How can you add a new field to a table (Select all that apply)?

A. Click the Insert Field button under the Tables tab in Datasheet view.

B. Enter data in the Click to Add column in Datasheet View.

C. Enter a field name and data type in the first blank column in Design View.

D. Click the desired field type button under the Fields tab in Datasheet View.

7. What is the unique identifier that specifies that data in that field may not be duplicated in any other of the table records called?

8. How can you automatically change the width of a column to accommodate the widest entry in that column?

A. Click the Accommodate button on the Ribbon.

B. Double-click inside any of the cells in the column you want to expand.

C. Right-click in the column you want to expand and choose AutoFit from the contextual menu.

D. Double-click on the right border of the column heading you want to expand.

9. How can you display the available Quick Start fields?

A. Click the More Fields button on the Ribbon under the Fields tab and scroll down to the Quick Start area.

B. Click the Quick Start button on the Ribbon under the Datasheet tab.

C. Click the Insert button on the Ribbon under the Datasheet tab and then click the Quick Start button.

D. Click the Templates button on the Ribbon under the Tables tab and then scroll down to the Quick Start area.

10. From Design view, how can you rearrange fields in your table?

11. You cannot enter data in a field that is set to AutoNumber.

A. True

B. False

12. Which of the following in NOT a field property?

A. Text

B. Format

C. Primary Key

D. Default Value

LAB 2 – ON YOUR OWN

1. Create a blank new database. Name the file Lab2 and save it in the Lesson Files folder.

2. Create a new table in Design view named tblContacts. Add the following fields to your table:

Field Name	Data Type
ContactID	AutoNumber
FirstName	Short Text
LastName	Short Text
Address	Short Text
City	Short Text
State	Short Text
Zip	Short Text
FirstContactDate	Date/Time
Active	Yes/No

3. Ensure that the ContactID field is set as a Primary Key Field.

4. In Design view, set the field size of the State field to only allow 2 characters.

5. Insert a new field named Phone above the address field. Set the Data Type to Short Text.

6. Switch to Datasheet view. Using Quick Start, add the Status field to your table. Position the Status field so that it is the last field in your table.

7. Switch to Design View and set the default value of the State field to: IL

8. In Datasheet view, enter your own address information into the table. Use today’s date as the contact date. Using the drop-down arrow, set the Status to “Waiting”.

9. Close the tblContacts table.

10. Close the database and exit Microsoft Access.

image1

image35.png

image36.png

image37.png

image38.png

image39.png

image40.png

image41.png

image42.png

image43.png

image44.png

image45.png

image46.png

image47.png

image48.png

image49.png

image50.png

image51.png

image52.png

image53.png

image54.png

image55.png

image56.png

image57.png

image58.png

image59.png

image60.png

image61.png

image62.png

image63.png