Access Module 2

Set a field's Format property in Table Design view.

Click the DateOfHire field.It is important to select the field you want to modify before working with the Field Properties pane in Table Design view.In the Field Properties pane, click the Format property.Clicking the Format property box displays the Format property arrow.Click the Format property arrow.A list appears with common format options for a Date/Time field, including Short Date, Medium Date, and Long Date.Click Short Date.A Short Date Format property formats the values in the field in a numeric month-day-year format with four digits for the year, such as 1/20/2018.

Set a field's Format property in Table Datasheet view.

Click the first value in the DateOfHire field.Selecting any value in the DateOfHire field allows you to change the Format property for the entire field.Click the Table Tools Fields tab on the Ribbon.The Table Tools Fields tab on the Ribbon provides options to modify the fields in the table.In the Formatting group, click the Format arrow.A list appears with Format property options for a Date/Time field.Click Medium Date.A Medium Date Format property formats the values in the field in a consistent day-month-year format. For example, the first date in the DateOfHire field for the first record is formatted as 01-Jan-11.

Open the Relationship window.

On the Ribbon, click the Database Tools tab.The Database Tools tab of the Ribbon has features that help you work with the entire database.In the Relationships group, click the Relationships button.The Relationships window opens and displays the relationships that were established between the three tables in this database.

Create a table in Design view.

1. Click the Create tab on the Ribbon. 2. In the Tables group, click the Table Design button. 3. Type RegionID as the first field name and press ENTER. 4. The first field in the table is entered and named RegionID. 5. After giving the field a name, you select its data type. 6. Click the Data Type list arrow for the RegionID field. 7. A list of data types for a field is presented. 8. Click AutoNumber. 9. Use the AutoNumber Data Type when you want Access to automatically number the records. 10. Click the second Field Name cell. 11. Type RegionName as the second field name and press ENTER. 12. The second field in the table is entered and named RegionName. Short Text is the default data type for new fields and is the desired data type for the RegionName field.Click the Save button on the Quick Access toolbar. 13. Type Regions in the Save As dialog box. 14. Table names should be descriptive yet as short as possible. 15. Click OK in the Save As dialog box. 16. Click No when prompted to create a primary key field at this time. 17. The primary key field is used to uniquely identify each record. It is not required for a new table but best practices encourage a primary key field for each table. You can allow Access to automatically add a primary key field to the table when you create it, or set the primary key field at a later time.

Add a caption to a field in Table Datasheet view.

1. In Datasheet view of the Managers table, click the DOH field to select it. 2. It's a good practice to select the field you want to modify before working with the features provided by the Ribbon. 3. Click the Table Tools Fields tab on the Ribbon. 4. The Fields tab provides buttons to add and modify fields in the table. 5. In the Properties group, click the Name & Caption button.The Name & Caption button opens the Enter Field Properties dialog box where you can modify the field name, caption, or description. 6. Click the Caption box & Type DateOfHire 7. Click OK 8. DateOfHire will now display instead of the actual field name, DOH, at the top of datasheets and as the default label for the DOH field in forms and reports.

Create a relationship between two tables

Click the Database Tools tab on the Ribbon.In the Relationships group, click the Relationships button.In the Relationships group on the Relationship Tools Design tab, click the Show Table button.Click StatesClick AddTo add a relationship to a table, the table must be added to the Relationships window.Click Close in the Show Table dialog box.Drag the StateAbbreviation field from the States table to the State field in the Stores table.The States and Stores tables participate in a one-to-many relationship because one record in the States table may be linked to many records in the Stores table using the common data in the linking fields. The Edit Relationships dialog box opens with options for creating the relationship.In the Edit Relationships dialog box, click the Enforce Referential Integrity check box.Click Create.A relationship line appears with a number 1 to indicate the "one" side of the relationship and the infinity symbol to demonstrate the "many" side.

Import data from a .txt file into an existing table.

Click the External Data tab on the Ribbon.In the Import & Link group, click the Text File button.Be sure to click the Text file button in the Import & Link group versus the Export group of the Ribbon.Click BrowseClick the NewRegions.txt fileThe NewRegions.txt file has the new records you want to import into the Regions table.Click Open in the File Open dialog box.Click the Append a copy of the records to the table option button.You can import the data to a new or existing table. If you choose an existing table, the fields in the text file need to be in the same order and of the same data type as the fields in the existing table.Click the drop-down list arrow.The list shows you a list of the current tables in the database.Click Regions.Click OK in the Get External Data - Text File dialog box.Viewing the data allows you to see how many records are in the text file and how they are ordered.Click Next to accept the Delimited option.The NewRegions.txt file is a tab-delimited text file. Tabs separate the data for each field in the records.Click Next to confirm the tab delimiter that separates the fields.The Text File Import Wizard reads the text file and correctly identifies that tab characters separate the field values.Click Finish to complete the import.You do not want to save the import steps for later use.Click Close to close the last dialog box that prompts you to save the import process.

Delete a field from a table in Design view

Click the Fax field.On the Table Tools Design tab, in the Tools group, click the Delete Rows button.Deleting a field removes it from every record in the table. Given that you cannot undo this action, you are prompted to make sure you want to delete the field.Click Yes.The Fax field is permanently deleted from the Stores table.

Rename a field in Table Design view.

Click the Field Name cell for the Address field.Select Address in the fieldname cell, type StreetAddress, and press ENTER.Field names should be as short as possible yet long enough to be clear and descriptive. Best database design practices encourage field names without spaces or special characters.

Move a field in Table Design view.

Click the ManagerID record selector to select the ManagerID field.Drag the ManagerID record selector up one field so that the black indicator line is positioned between the StoreID and MetroArea fields.

Move a field in Table Datasheet view.

Click the MetroArea column header field name to select it.Drag the selected MetroArea column header two fields to the right so that the black indicator line is positioned between the Phone and Address fields, then release the mouse button.

Add a description to a field in Table Design view.

Click the RevenueGoal field's Description cell.A short description of the Description property appears in the lower-right corner of the Field Properties pane.Type Annual revenue quota and press ENTER.Annual revenue quota will now document the field in Table Design view and display in the status bar when the RevenueGoal field has the focus in Form view.

Set a field's field size property in Table Design view.

Click the State field.In the Field Properties pane, click the Field Size property box.Select the text in the Field Size property box, type 2 to replace the value and press ENTER.All of the entries in the State field are 2-character state abbreviations, so setting the Field Size value for the State field to 2 will lower database storage requirements and prevent users from entering more than 2 characters in the field.Click the Save button on the Quick Access toolbar.Click Yes at the prompt.Because no current State field values are more than 2 characters, no data will be lost.

Add a field to a table using the More Fields list in Table Datasheet view.

In Datasheet view of the Managers table, click the first value in the LastName field.When creating fields with the More Fields option, the new field column will be positioned just to the right of the column where the insertion point is located.On the Ribbon, click the Table Tools Fields tab.The options on the Table Tools Fields tab help you add and modify fields in Datasheet view.In the Add & Delete group, click the More Fields button.A list of data types and field options appears in the list.In the Date and Time section, click the Medium Date option.A new column is inserted to the right of the LastName field. The default field name, Field1, is highlighted, prompting you to rename the new field.Type DOB to replace the default field name of Field1, and press ENTER.The new DOB (Date of Birth) field has been created and is ready for you to enter data into it.

Set a field's caption property in Table Design view.

In Design View of the Managers table, click the DOB field to select it.Click the Caption property in the Field Properties pane.Type DateOfBirth and press ENTERDateOfBirth will now display instead of the actual field name, DOB, at the top of datasheets and as the default label for the DOB field in forms and reports.

Add a field to a table in Table Design view.

In Design View of the Stores table, click the empty Field Name cell just below the Zip field.Type WebSite in the cell and press ENTER.Field names should be as short as possible yet clear and descriptive. Best database design practices encourage field names without spaces or special characters.Click the Data Type list arrow.Click Hyperlink.Hyperlink is the best data type for this field as it will store web site address information.

Set a table's primary key in Design view

In Table Design view click the StoreID field.In the Tools group on the Table Tools Design tab, click the Primary Key button.The selected field, StoreID, is set as the primary key field, as evidenced by the key symbol to the left of the field name.

Set a field's data type in Table Design view.

In Table Design view, click the Data Type cell for the Zip field.Click the Data Type list arrow for the Zip field.A list of possible data types to assign to the field appears.Click Short Text on the list.The Short Text data type is more appropriate than the Number data type for a Zip field because some zip codes start with 0 (zero). The Number data type does not save 0 as a leading character in the entry.

Set a field's Decimal Place property in Design view.

In Table Design view, click the RevenueGoal field.It is important to select the field you want to modify before making any property changes.In the Field Properties pane, click the Decimal Places property.A short description of the selected property appears in the lower-right corner of the Field Properties pane.Type 0 (zero) and press ENTER.Now values in the RevenueGoal field will round to the nearest dollar versus the nearest cent.

Import a table from another Access database.

On the Ribbon, click the External Data tab.The buttons on the External Data tab allow you to import or export data into or out of an Access database.In the Import & Link group, click the Access button.The Get External Data - Access Database dialog box opens with options to import or link to external data.Click Browse.The File Open dialog box opens which allows you to choose a database file or navigate to a new folder. The USAStates.accdb database is in the current folder.Click the USAStates.accdb database.Click Open.You want to import versus link to the States table. By default, the import option is already selected.Click OK.You can import several objects in the same process by clicking the appropriate object tab and clicking the object you want to import. Given you want to import one table, you are ready to select it.Click States.You may import as many different objects from the different object types as desired in one process. In this case, you only want to import one table, the States table.Click OK.If you import the same objects on a regular basis you can save the import steps. In this case, you do not want to save the steps.Click Close.The States table has been imported from the USAStates database to the IceCreamFranchises database. It is listed as a table in the Tables section of the Navigation Pane.

Import data from an Excel workbook into an existing table.

On the Ribbon, click the External Data tab.The buttons on the External Data tab allow you to import or export data into or out of an Access database.In the Import & Link group, click the Excel button.The Get External Data - Excel Spreadsheet dialog box opens with options to import or link to external data.Click Browse.The File Open dialog box opens, which allows you to choose a workbook file or navigate to a new folder. The Provinces.xlsx workbook is in the current folder.Click the Provinces.xlsx workbook.Click Open.You want to append the data to the StatesOrProvinces table.Click the Append a copy of the records to the table option.You must choose the target table identified in the list to the right of the Append a copy of the records to the table: option.Click the Table arrow.Click StatesOrProvinces.Click OK.The Import Spreadsheet Wizard provides a preview of the records you are about to import and append.Click Next.The Import Spreadsheet Wizard is ready to import the records to the StatesOrProvinces table and prompts you whether you want to analyze the data after importing it. You do not want to choose this option at this point.Click Finish.You can save the import steps if you need to repeat the import process later. In this case you do not want to save them.Click Close.The records in the Provinces.xlsx workbook have been appended to the StatesOrProvinces table in the IceCreamFranchises database.

Set a field's Default Value property in Table Design view.

Set a field's Default Value property in Table Design view. AC 5567 Observe mode In Table Design view, click the Location field.It is important to select the field you want to modify before making any property changes.Click the Default Value property in the Field Properties pane.A short description of the selected property appears in the lower right corner of the Field Properties pane.Type Mall and press ENTER.Access automatically adds the "quotation marks" around a textual criterion such as the Default Value entry for a Short Text field.