New Perspectives Access 365 | Module 2: End of Module Project 1 Bowles College
IMPROVING AN ACCESS DATABASE
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_EOM2-1_FirstLastName_1.accdb as NP_AC365_EOM2-1_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. To complete this Project, you will also need the following files:
Support_AC365_EOM2-1_Departments.xlsx
Support_AC365_EOM2-1_Students.txt
3. With the file NP_AC365_EOM2-1_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. As a student affairs coordinator at Bowles College, you have been developing an Access database application to maintain information about student groups. You need to improve the database by importing data and modifying fields and tables. Import the text file Support_AC365_EOM2-1_Students.txt and append the records to the Students table. Import the data in a Delimited format using a comma delimiter. Do not save the import settings. Open the Students table in Datasheet View to review the data, a portion of which is shown in Figure 1, and then close it.
Figure 1: Students Table in Datasheet View
2. Import the Excel workbook named Support_AC365_EOM2-1_Departments.xlsx as a new table to store academic department data. The first row of the spreadsheet contains column headings. Use the default field options. Set the DeptID field as the primary key. Name the new table Departments and do not save the import settings. Open the Departments table in Datasheet View, and then resize the DeptName column to its best fit to review the data, a portion of which is shown in Figure 2. Save and close the Departments table.
Figure 2: Departments Table in Datasheet View
3. Open the Groups table in Design View and change the Field Size of the GroupType field to 50 from 255 to reduce the field size. Change the Required property from No to Yes for the GroupType field to require data in this field.
4. With the Groups table open in Design View, add Public as the Default Value property of the Office field since most offices are public. Use 0 as the Decimal Places property values for the Members field because the values must be whole numbers. Save the Groups table. Click Yes when notified some data may be lost and when prompted to test data integrity.
5. Update the structure of the Groups table by adding a new field after the Members field named Fees with the Currency data type. Move the Office field so it becomes the last field in the table. Save the Groups table and display it in Datasheet View, as shown in Figure 3, and then close the Groups table.
Figure 3: Groups Table in Datasheet View
6. Open the Advisors table in Design View. After the Budget field, add a new field named DeptID with the Short Text data type to provide a linking field to the Departments table.
7. With the Advisors table open in Design View, delete the OfficeNumber field, which contains no data. Change the data type of the Budget field to Currency because it stores dollar amounts. Set the Default Value property to 1200 for the Budget field. Save the Advisors table and click Yes when notified some data may be lost.
8. Open the Advisors table in Datasheet View, and then change the format of the StartDate column to Medium Date to show the dates with an abbreviated month name and two-digit year. Resize all columns to their best fit, as shown in Figure 4. Save and close the Advisors table.
Figure 4: Advisors Table in Datasheet View
9. Open the Relationships window and then add the Departments and Advisors tables. Create a one-to-many relationship between the primary Departments table and the related Advisors table using the DeptID field as the common field. Enforce referential integrity on the relationship, and then save and close the Relationships window.
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.