New Perspectives Access 365 | Module 1: SAM Project B Reliable Delivery

GETTING STARTED

1. Save the file NP_AC365_1B_FirstLastName_1.accdb as NP_AC365_1B_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_AC365_1B_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. You are a project manager for Reliable Delivery, a parcel delivery company in Ottawa, Canada, that provides parcel delivery services to other businesses and individuals throughout Canada. To better manage the drivers and the types of deliveries they provide, you have decided to create an Access database. Start by creating a new table as follows to list the provinces where the company has customers:

a. Create a table in Datasheet View.

b. Replace the default primary key ID field name with ProvinceCode and assign the Short Text data type to the field. ProvinceCode should remain the primary key.

c. Add a field with the name ProvinceName and the Short Text data type to the table. Save the table with the name Province but do not close it.

2. Add the six records shown in Table 1 to the Province table to represent six Canadian provinces. Save and close the table.

Table 1: Province

ProvinceCode | ProvinceName ON | Ontario QC | Quebec BC | British Columbia AB | Alberta MB | Manitoba SK | Saskatchewan

3. Create a new table as follows to store data about the customers of Reliable Delivery:

a. Create a table in Design View.

b. Add a field with the name CustomerID and the AutoNumber data type to have Access generate values for this field.

c. Set the CustomerID field as the table's primary key. Save the table with the name Customer but do not close it.

4. Add another field to the Customer table with the field name CustomerName and the Short Text data type. Save the table and then close it.

5. Open the Driver table in Design View. Change the name of the Last field to LastName to make it more precise.

6. Still in the Driver table, change the Data Type property for the DriverID field to Short Text, which is more appropriate for data that will not be used in calculations.

7. Add another field to the Driver table after the LastName field to include the date each driver started working at Reliable Delivery. Name the new field StartDate and assign it the Date/Time data type. Save and close the Driver table.

8. Open the Driver table in Datasheet View. Delete the twelfth record in the Driver table (which has a DriverID field value of 511244) because that driver no longer works for Reliable Delivery.

9. Still in the Driver table, change the value for the FirstName field of the thirteenth record (which has a DriverID value of 511289) to Nikolaj to correct an error. Close the Driver table.

10. You also need to view individual driver records. Create a form for the Driver table with the following options:

a. Use the Form Wizard to create a form that contains all fields from the Driver table.

b. Select the Columnar layout for the form. Save the form with the name Driver Data Form and confirm that the form matches Figure 1. Close the form.

Figure 1: Driver Data Form in Form View

11. Use the Query Wizard to create a query based on the Delivery table that includes all fields from the Delivery table to list the delivery codes and names. Save the query with the name Delivery Query (which is the default name) and then close the query.

12. Your supervisor has asked for a printed copy of the Delivery table. Create a report based on the Delivery table with the following options:

a. Use the Report Wizard to create a report that includes all fields from the Delivery table.

b. Do not add any grouping levels to the report.

c. Sort the report by the DeliveryCode field in Descending order to organize the data.

d. Select the Tabular layout with Portrait orientation for the report. Save the report with the name Delivery Report. Confirm that the report matches Figure 2 and then close the report.

Figure 2: Delivery Report in Report View

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.