CIS 110 CC Creating a New Table to Store Officer Information Project

1. Open the NewYorkDMV-05.accdb start file.
2. Save a new copy of your database as [your initials] Access 5-5.
3. Enable content in the database.
4. Create a new table to store officer information.
a. Click the Table Design button to open a new table in Design view.
b. Enter the table fields using the information in Table 5-11.
c. Designate the OfficerID field as the primary key.
d. Save the table as Officers.
Table 5-11
Field
Name
Data Type
Description
Field
Size
OfficerID
Short
Text
Officer’s unique ID/Badge
number
9
FirstName
Short
Text
Officer’s first name
20
LastName
Short
Text
Officer’s last name
20
DateHired
Date/Time
Date of employment
Format
Short
Date
5. Add the data into the table.
a. Switch to Datasheet view. Enter the records shown in Table 5-12 into the
Officers table.
b. Close the table.
Table 5-12
OfficerID
FirstName
LastName
DateHired
A0002121
1
Reggie
DeFrank
4/1/2018
A0003256
7
Alex
Rodrigue
z
12/20/201
6
A0004572
9
Robert
Chung
3/17/2014
A0005659
1
Jackie
Markley
6/2/2000
A0005770
6
Rupal
Jain
2/12/2016
A0006140
3
Carlos
Villanuev
a
10/1/1998
A0006144
4
Amand
a
Freed
9/2/2006
A0009238
5
Javier
Torres
6/13/2018
6. Review the existing Drivers table.
a. Open the Drivers table in Datasheet view.
b. 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
6. 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.
7. Close the Drivers table.
8. Review the junction table.
a. 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.
b. Select the OfficerBadgeNumber field.
c. 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.
9. Use the Lookup Wizard to create a one-to-many relationship between Officers and
Tickets. This lookup will display multiple fields.
a. Select Lookup Wizard in the Data Type property box of the
OfficerBadgeNumber field to create a relationship between the Officers and
Tickets tables.
b. Choose to look up a field from the Officers table.
c. Include the OfficerID, FirstName, and LastName fields in the Selected Fields
window.
d. Sort by the LastName field in ascending order.
e. Do not hide the key column.
f. Store the OfficerID field.
g. Enable data integrity and restrict deletes.
h. Save and close the table.
10. Edit the relationship.
a. 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
B. Edit the relationship between Officers and Tickets to cascade updates.
C. Save the changes.
D. Close the Relationships window.
6. Edit the Tickets table to add a date-stamp field.
a. Open the Tickets table in Design view.
b. 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
c. Save the table.
7. Create an event-driven data macro to store the time stamp when a record is added to
the Tickets table.
a. Create a Before Change event-driven data macro.
b. 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.
c. 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.
d. Add an If action below the second Comment and enter [IsInsert]=True into
the If box.
e. 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.
f. 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.
G. Save and close the macro.
Figure 5-116 Macro code for the Before Change event-driven macro
6. Test the macro.
a. Switch to Datasheet view if necessary.
b. 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.
c. Widen the RecordAddedTimeStamp field to see the contents. Make a note of
the current value, including the time.
d. 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.
e. Save and close the table.
7. Close the database.
8. Upload and save your project file.
9. Submit project for grading.