pVIEW:InstructionsPrevious AttemptsBest Practices
Guided Project 5-1
For this project, you create a new database based on the Faculty template. You explore the functionality of the database, modify the table and query, and enhance the database by adding a new table and creating a relationship using the Lookup Wizard.
[Student Learning Outcomes 5.1, 5.2]
File Needed: Faculty-05.accdb (Available from the Start File link.)
Completed Project File Name: [your name]-Faculty-05.accdbSkills Covered in This ProjectBuild a database from a template.Review and modify template objects.Create a new table.Define a relationship with the Lookup Wizard.Use multiple columns in a lookup field.View and edit relationship properties.Edit properties of the lookup field. Open the Faculty-05 database start file. Enable content in the database. Close the Faculty List form.Figure 5-96 Navigation Pane showing all objectsExplore the Navigation Pane, objects, and relationships. Click the Shutter Bar Open/Close Button to open the Navigation Pane. Click the Navigation Pane arrow to open the Navigation menu. Select the Object Type category. The Navigation Pane updates to show all of the objects (Figure 5-96). Open the Faculty Details form to get a sense of the design of this template. Close the Faculty Details form. Open the Faculty List form. Click the Reports drop-down arrow on the Faculty List form and select the All Faculty report. The report displays, but no faculty records display. Close the report. Close the Faculty List form.Modify the Faculty table because several fields are not needed and others require changes. Open the Faculty table in Design view. Select the Company field and click the Delete Rows button [Table Tools Design tab, Tools group]. Click Yes in the Message Box informing you that the index for this field will also be deleted. Delete the ID Number, Fax Number, Country/Region, and Web Page fields. Change the Field Name of the Education Level/Degree field to Highest Degree . Select the Department field. Click the Lookup tab in the Field Properties area. Edit the Row Source property and add ;“Art”;“History”;“Music” to the list.Figure 5-97 Context menu Save and close the table.Edit the Faculty Extended query. Open the Faculty Extended query in Design view. This query uses the company field you deleted from the table. As a result, you need to update the query. Click the File As cell in the Field row, right-click to open the context menu, and select the Zoom button (Figure 5-97) to open the Zoom window. Delete [Company], from the expression. Note: You must delete the comma that appears after [Company] for the query to work correctly. Click OK. Click the Contact Name cell in the Field row, right-click to open the context menu, and select the Zoom button to open the Zoom window. Delete [Company], from the expression. Click OK. Save and run the query. The query executes but no records display. Close the query.Explore the Faculty Details form and add records into the database. Open the Faculty Details form. Note that the form opens in a separate window and you cannot switch to Design view while the form is in Form view. This is a result of property settings and macros attached to the form. Click the First Name text box and enter Bruce . Note that the Web Page, Fax Number, and Country/Region text boxes display #Name? because the links back to the table fields are invalid. Click the Last Name text box and type Elkins . Click the Save and New button on the form. Click the First Name text box and enter Charlotte . Note that the Web Page, Fax Number, and Country/Region text boxes display #Name? because the links back to the table fields are invalid. Click the Last Name text box and type Liu . Click the Save and New button on the form. Close the form. Don’t make any changes to remove the text boxes that are no longer valid in this exercise because you will not use this database later, although those changes must be made before putting the database into operation.Add a second table into the database. Click the Table Design button [Create tab, Tables group]. Add fields into the table using the information in Table 5-9.Table 5-9Field NameData TypeDescriptionFieldSizeDegreeID AutoNumberUnique identifier for each degree ID-FK NumberMust match a value in the ID field in the Faculty table Degree Short TextType of degree earned 50 DegreeYear NumberYear degree was awarded Institution Short TextInstitution granting the degree 50 Designate the DegreeID field as the primary key. Select the Degree field. Click the Lookup tab. Select Combo Box in the Display Control property box. Select Value List in the Row Source Type property box. Type “High School Diploma”;”G.E.D.”;” Bachelor’s Degree”;”Master’s Degree”;” Ph.D.” in the Row Source property box. Select the DegreeYear field and click the General tab. Type #### in the Input Mask property box. Save the table as Degrees .Create a relationship using the Lookup Wizard.Figure 5-98 Select fields in the Lookup Wizard Click the Data Type property box of the ID-FK field, click the drop-down arrow to display the list of data types, and then select Lookup Wizard to launch the wizard. Select the I want the lookup field to get the values from another table or query radio button and click Next. Select the Faculty table and click Next. Select ID from the list of Available Fields and click the Move Single Field button to move the field into the Selected Fields window. Move the First Name and Last Name fields (Figure 5-98). Click Next. Don’t sort on any fields and click Next. Deselect the Hide key column check box and click Next. Select the ID field to store in the table and click Next. Check the Enable Data Integrity box and the Cascade Delete radio button. Click Finish to complete the wizard. A Lookup Wizard dialog box displays. Click Yes to save the changes to the table so that Access can create the relationship. Select the ID-FK field and click the Lookup tab. Click the Column Widths property box. Change the entry so it reads .5″;1″;1″ . The ID column does not need to be as wide as the name columns. Save the table. If a dialog box displays informing you that the table has changed since the last time you opened it, click Yes to save your changes. Close the table.Edit the relationship. Click the Relationships button [Database Tools tab, Relationships group] to open the Relationships window. Select the relationship line. Click Edit Relationships [Relationship Tools Design tab, Tools group]. Select the Cascade Update Related Fields check box. Click OK. Click Save to save the changes made to the relationships. Close the Relationships window.Add records into the Degrees table. Open the Degrees table in Datasheet view. Click the ID-FK field, click the drop-down arrow and select Bruce Elkins (Figure 5-99). The DegreeID column is narrower because of the property changes you made in step 11o.Figure 5-99 Drop-down list for the ID-FK field Select Ph.D. from the drop-down list in the Degree field. Type 2012 in the DegreeYear field. Type University of Arizona in the Institution field. Click the ID-FK field in the second row, click the drop-down arrow, and select Charlotte Liu. Select Ph.D. from the drop-down list in the Degree field. Type 2015 in the DegreeYear field. Type Rutgers in the Institution field. Save and close the table.Test the cascade delete property of the relationship. Open the Faculty table in Datasheet view. Click the plus sign on Bruce Elkin’s row to see his related degree records. Click the minus sign. Click the row selector box for Bruce Elkin’s record in the table. Click the Delete button [Home tab, Records group]. A warning message displays alerting you that you will delete related records in another table. Click Yes. Close the table. Open the Degrees tables in Datasheet view to verify that the record was deleted. Close the table. Save and close the database. Upload and save your project file. Submit project for g
lease answers these questions below 2 projects VIEW:InstructionsPrevious AttemptsBest Practices
Independent Project 5-5
The New York Department of Motor Vehicles wants to extend the functionality of its database. Add a table to store information about their officers and have that table relate to the tickets table. Finally, build and test an event-driven data macro to automatically add a time stamp when a ticket is added.
[Student Learning Outcomes 5.2, 5.3, 5.4, 5.6, 5.7]
File Needed: NewYorkDMV-05.accdb (Available from the Start File link.)
Completed Project File Name: [your name]-NewYorkDMV-05.accdbSkills Covered in This ProjectEdit a junction table with a unique primary key.Define a relationship with the Lookup Wizard.Use multiple columns in the lookup field.Use the Macro Builder.Build an event-driven data macro.Use conditions in a macro.Test a macro.Add the current date to a form.Preview a report. Open the NewYorkDMV-05.accdb start file. Save a new copy of your database as [your initials] Access 5-5 . Enable content in the database.Create a new table to store officer information. Click the Table Design button to open a new table in Design view. Enter the table fields using the information in Table 5-11. Designate the OfficerID field as the primary key. Save the table as Officers .
Table 5-11Field NameData TypeDescriptionField SizeFormatOfficerID Short TextOfficer’s unique ID/Badge number 9 FirstName Short TextOfficer’s first name 20 LastName Short TextOfficer’s last name 20 DateHired Date/TimeDate of employment Short DateAdd the data into the table. Switch to Datasheet view. Enter the records shown in Table 5-12 into the Officers table. Close the table.
Table 5-12OfficerIDFirstNameLastNameDateHiredA00021211 Reggie DeFrank 4/1/2018 A00032567 Alex Rodriguez 12/20/2016 A00045729 Robert Chung 3/17/2014 A00056591 Jackie Markley 6/2/2000 A00057706 Rupal Jain 2/12/2016 A00061403 Carlos Villanueva 10/1/1998 A00061444 Amanda Freed 9/2/2006 A00092385 Javier Torres 6/13/2018 Review the existing Drivers table. Open the Drivers table in Datasheet view. Click the plus sign to expand the first record, LicenseNumber 10000501, to view the related records in the Tickets table (Figure 5-114). Note that the Tickets table already has an OfficerBadgeNumber field.Figure 5-114 Drivers table and related records from the Tickets table Determine the relationship type. A driver can have many different tickets. An officer can have written many different tickets. There is a many-to-many relationship between records in the Drivers and Officers table. This relationship is via the Tickets table. The Tickets table is the junction table. Close the Drivers table.Review the junction table. Open the Tickets table in Design view. Review that the TicketNumber field is the primary key. Note that this table has the two foreign keys of DLNumber and OfficerBadgeNumber. If a junction table has its own unique identifier, like TicketNumber, then it does not need to use a concatenated primary key. However, the foreign keys must still be in the table to provide the relationship. Select the OfficerBadgeNumber field. Change the description of the OfficerBadgeNumber field to read Must match an OfficerID in the Officers table . The description doesn’t affect the database but makes the table design more understandable.Use the Lookup Wizard to create a one-to-many relationship between Officers and Tickets. This lookup will display multiple fields. Select Lookup Wizard in the Data Type property box of the OfficerBadgeNumber field to create a relationship between the Officers and Tickets tables. Choose to look up a field from the Officers table. Include the OfficerID, FirstName, and LastName fields in the Selected Fields window. Sort by the LastName field in ascending order. Do not hide the key column. Store the OfficerID field. Enable data integrity and restrict deletes. Save and close the table.Edit the relationship. Click the Relationships button [Database Tools tab, Relationships group] to open the Relationships window (Figure 5-115). If all three tables do not display, click the Show Table button to add the missing tables into the window. Recall that you previously created the relationship between Drivers and Tickets in chapter 2.Figure 5-115 New York DMV database relationships Edit the relationship between Officers and Tickets to cascade updates. Save the changes. Close the Relationships window.Edit the Tickets table to add a date-stamp field. Open the Tickets table in Design view. Add a new field using the information below: Field Name: RecordAddedTimeStamp Data Type: Date/Time Description: Store the date and time the record was added to the table Format: General Date Save the table.Create an event-driven data macro to store the time stamp when a record is added to the Tickets table. Create a Before Change event-driven data macro. Add a Comment action into the Macro Builder and type This macro adds a date/time stamp into the RecordAddedTimeStamp field in the Tickets table. Add a second Comment action and type The IsInsert function checks to see if this is a record being added versus a change. The time stamp should only be set at the time the record is first added into the table. Add an If action below the second Comment and enter [IsInsert]=True into the If box. Add a Comment directly below If and enter Set the value of the RecordAddedTimeStamp field to the current date/time. The =Now() function is used to get the current date/time from the system. Add a SetField action below the Comment, enter RecordAddedTimeStamp in the Name argument and enter Now() in the Value argument. The completed macro is shown in Figure 5-116.Figure 5-116 Macro code for the Before Change event-driven macro Save and close the macro.Test the macro. Switch to Datasheet view if necessary. Add the following record into the table. When entering the OfficerBadgeNumber, use the drop-down list and verify that all three fields show in the list. TicketNumber: 4235896919 DLNumber: 10004372 TicketDate: 3/17/2020 City: Syracuse PrimaryFactor: Unsafe Speed OfficerBadgeNumber: A00057706 Fine: 90 Press the down arrow key after entering the Fine. Upon leaving the record, the RecordAddedTimeStamp field updates. Most likely the field displays ####, indicating the field is not wide enough. Widen the RecordAddedTimeStamp field to see the contents. Make a note of the current value, including the time. Change the PrimaryFactor to Driver Distraction . Press the down arrow key to leave the record and have the changes saved. Verify that the contents of the RecordAddedTimeStamp field did not change. Save and close the table. Close the database. Upload and save your project file. Submit pr