Homework answers / question archive / New Perspectives Access 2019 | Module 1 Sam Project 1a Global Human Resources Consultants CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED Open the file NP_AC19_1a_FirstLastName_1

New Perspectives Access 2019 | Module 1 Sam Project 1a Global Human Resources Consultants CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED Open the file NP_AC19_1a_FirstLastName_1

MS Access

Share With

---

New Perspectives Access 2019 | Module 1 Sam Project 1a

Global Human Resources Consultants

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS

GETTING STARTED

Open the file NP_AC19_1a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as NP_AC19_1a_FirstLastName_2.accdb by changing the “1” to a “2”.

If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

IMPORTANT: Open the _GradingInfoTable table (in the navigation pane on the left) and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. You are a project manager for Global Human Resources Consultants, an organization that provides human resources services to emerging companies located in eastern Europe. To better manage consultants and the projects on which they work, you have decided to create an Access database.

Create a new table in Datasheet View with the following options:

a. Rename the default primary key ID field (this is the first field on the left) CountryCode and change the data type to Short Text. (If you don’t see an option to change the data type, click on the Fields tab on the Ribbon, then click on the drop-down arrow beside Data Type and choose the “Short Text” format.)

b. Change the field size of the field to 3. (Change this on the Fields tab in the Field Size box.)

c. Add a new field with the name CountryName and the Short Text data type. Save the table, using Country as the new name. (Whenever the instructions say to “Save the table”, you only need to click on the Save icon on the Quick Access Toolbar in the top left of the screen. This allows you to save the Table, Report, etc.)

2. With the Country table still open in Datasheet View, add the four records shown in Table 1 below. Save and close the Country table. (If you need to switch to the Datasheet View, click on the View button on the far left of the Home and Fields Ribbons.)

3. Create (click on the Create tab) a new table in Design View to store the clients of Global as follows:

a. Add a field with the name ClientID and the AutoNumber data type.

b. Apply ClientID as the table's primary key. (You should be able to right-click and choose “Primary Key.”) Save the table with the name Client but do not close it. (If you accidentally close it, just double-click on the table in the Navigation Pane on the left to reopen it.)

4. With the Client table still open in Design View, add a second field to the table with the field name ClientName and the Short Text data type. Save the table and then close it.

5. Open the Consultant table in Design view and update the name of the First field, using FirstName as the new value, then update the Last field, using LastName as the new value.

6. With the Consultant table still open in Design View, change the data type property for the ConsultantID field from Number to Short Text.

7. In Design View, add a new field to the Consultant table after the LastName field. The field should be named StartDate and have the Date/Time data type. Save and close the Consultant table.

8. Open the Consultant table in Datasheet View, navigate to the seventh record (which has a ConsultantID field value of 110460), and delete the record (right-click and click on “Cut” on the Ribbon or “Delete Record” on pop-up menu).

9. With the Consultant table still open in Datasheet View, navigate to the fourth record (which has a ConsultantID field value of 110351) and update the Firstname record, using Sidney as the new value. Close the Consultant table.

10. You also need to view individual consultant records. Use the Form Wizard (this is on the Create tab) to create a form for the Consultant table with the following options. (You can easily follow the steps in the box that opens, but make sure you choose the “Consultant” table from the list of Tables/Queries.)

a. Include all fields from the Consultant table in the form. (There is an arrow that allows you to choose all of the fields. Refer to the textbook or training if you forgot how to do this step.)

b. Select Columnar as the layout for the form. Save the form with the name Consultant Data Form and confirm that the form matches Figure 1. Close the form. (If you are asked to close the form in order to rename it, you can close the form, then right-click and rename it from the Navigation Pane on the left of the screen.)

11. Use the Simple Query Wizard (this is also on the Create tab) to create a query based on the Skill table with the following options. (Again, you can easily follow the steps in the box that opens, but make sure you choose the “Skill” table from the list of Tables/Queries.)

a. Include all fields from the Skill table in the query. Save the query with the name Skill Query (which is the default name) and then close the query. (If you are asked to close the Query in order to rename it, you can click on the Save icon in the top left, or close the query, then right-click and rename it from the Navigation Pane on the left of the screen.)

12. You (would like) to refer to a printed copy of the Skill table when assigning consultants to projects. Use the Report Wizard (also on the Create tab) to create a report based on the Skill table with the following options:

a. Include all fields from the Skill table in the report.

b. Do not include any grouping levels.

c. Sort the report by SkillCode in ascending order.

d. Select Tabular as the layout of the report and Portrait as the orientation of the report. (Tabular and Portrait may already be selected.) Save the report with the name Skill Report. Confirm that the report matches Figure 2 and then close the report. (If you are asked to close the Report in order to rename it, you can click on the Save icon in the top left, or close the report, then right-click and rename it from the Navigation Pane on the left of the screen.)

13. In the Navigation Pane, rename the Skill form, using Skill Data Form as the new value. Save and close any open objects (Tables, Queries, Forms, or Reports) in your database, but don’t close the whole database file yet! Compact and repair your database (from the File tab), close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.