access
MIS303Microsoft Access Assignment #1
Page 1 of 4
MIS303 Microsoft Access Assignment #1
Note (Important):
(1) This is an individual assignment. It accounts for 9% of your grade.
(2) Your final solution should be ONE Microsoft Access file (.accdb file) that is
submitted via Blackboard. Make sure that you submit the actual Access file. Do
NOT submit the temporary lock file (.laccdb file) that I will not be able to open.
Otherwise, you will get a 0 grade. To avoid making the mistake of submitting the
wrong temporary file, you should close all Microsoft Access programs first before
uploading your file as explained in the lessons and tutorials.
(3) Pay attention to details: all questions are detail-oriented. Make sure you read the
instruction word by word carefully. Otherwise, you might lose points for failing to
fulfill any of the clearly specified requirements.
(4) You may want to submit your solution at least 30 minutes before the deadline. Make
sure you click “Submit” rather than “Save Draft” to actually submit. You are required
to double-check your submission by downloading it and opening it to see if it is the
correct file. You will be responsible for any technical difficulty or mistake on your
side that results in submitting the wrong work or your work not being submitted or
being accepted as late. No exceptions will be made.
(5) Make good use of the videos and tutorials.
Microsoft Access Assignment #1 (Total: 50 pts)
A1 Home Improvement (AHI) provides installation services for home improvement in
Northern Virginia. AHI does not employ installers, but rather subcontracts the installations
out to a list of approved installers. Whenever a customer requests an installation service in the
store, a contract is created for the installation. AHI offers different payment options such as
20% deposit+80% after completion, 30% deposit+70% after completion, etc. AHI decides a
deposit amount based on the installation job and the customer pays upfront. Then AHI will
assign an installer for the installation job in the contract. Afterwards, the installer will go to
the customer’s house and complete the installation. Currently, this process is primarily paper-
based and does not provide management with the information needed to efficiently monitor
contracts and installers. AHI often runs advertisements for specials such as guaranteed 2-week
installation for ceiling fans. Without a way to effectively manage installations, installation
service sometimes does not get completed within the promised timeframe which has resulted
in customer dissatisfaction.
You are hired by AHI to create a database that helps automatically tracks installation
contracts and installers. The database must keep track of each AHI customer, including their
unique customer ID, first name, last name, street address, city, state, zipcode, and phone
number. It also keeps track of all approved installers, including their unique installer ID, SSN
(i.e., social security number), first name, last name, city, state, zipcode, phone number, and
daily charge (i.e., how much AHI pays to the installer for one day’s work). In addition, the
database records each payment option, including unique payment option ID and deposit
percentage. In order to provide management with the information needed to effectively track
installations, the database keeps track of the details for each installation contract, including
MIS303 Microsoft Access Assignment #1
Page 2 of 4
unique contract ID, the ID of the customer who requests the service, the ID of the installer
assigned to complete the installation, description of the requested installation job, the service
charge amount (i.e., how much the customer need to pay to AHI for the installation service),
the ID of the payment form, the date the contract is created, the date an installer is assigned to
work on the installation, the date the installation is completed by the installer, and the
customer’s rating of the job performed by the installer after the job is finished.
In the first task, you have created the ER diagram for the database as shown below.
Below is a list of additional business rules:
1) Any first name or last name is between 1 and 20 characters and is required.
2) Any city name is between 1 and 25 characters and is required.
3) ContractDescription is between 1 and 200 characters and is required.
4) CustomerID, InstallerID, and ContractID are Incremental AutoNumbers when
serving as primary keys.
5) ContractInstallerRating takes integer values between 0 and 100 (hint: use this
information to determine the Data Type and Field Size of the attribute; no need to
worry about Validation Rule that was not covered in the lessons).
6) All dates are in the Short Date format and have the Short Date input mask.
7) All telephone numbers have the (XXX) XXX-XXXX input mask.
8) Any SSN has the XXX-XX-XXXX input mask.
9) All the currency fields have 2 decimal places.
Your second task is to convert the above conceptual model plus business rules into a physical
model using Microsoft Access 2016 by working on Q1-Q6 (next page).
MIS303 Microsoft Access Assignment #1
Page 3 of 4
Q1. Create all the tables in the database. Make sure that: (30 pts)
• Each attribute has a meaningful description to explain its meaning.
• Each attribute has an appropriate Data Type and Field Size based on the above
business rules and/or the data provided (on next page and in the Excel file) and/or
domain knowledge.
• Business rules related to format, decimal places, and input mask are implemented.
Q2. Establish the relationships among the tables with referential integrity enforced for each
relationship. (10 pts)
Q3. Enter data for tables PaymentOption, Installer, and Customer (shown on next page) into
the database. (4 pts)
Q4. Import data from the Excel file Contract.xlsx into the Contract table. (1 pts)
Q5. Create a columnar form that allows personnel to add new customers or update existing
customer information. Include three buttons in the form: one button for moving to
previous record, one button for moving to next record, and one button for saving the
current record. Change the form title to “Customer Data Entry Form”. (4 pts)
Q6. Name the Access database exactly as your last name followed by your G number without
“G” and then followed by suffix “_Access_1” (there should not be extra spaces or
underlines in between; e.g. Ye12345678_ Access_1.accdb with .accdb being the file
extension) and submit it via Blackboard. (1 pts)
MIS303 Microsoft Access Assignment #1
Page 4 of 4
Table: Customer
CustomerID CustomerFirstName CustomerLastName CustomerStreet CustomerCity CustomerState CustomerZipCode CustomerPhone
1 Alex Anderson 1462 Stanford Dr Woodbridge VA 22193 (570) 231-5714
2 Jonah Park 325 Cherrywood Ct McLean VA 22101 (301) 561-4607
3 Jennifer Smith 106 Pickett Rd Fairfax VA 22030 (703) 761-0732
4 Aaron Goodman 715 Adelphi Ln Vienna VA 22180 (571) 158-1135
Table: Installer
InstallerID InstallerSSN InstallerFirst
Name
InstallerLast
Name
Installer
City
Installer
State
Installer
ZipCode
InstallerPhone InstallerDaily
Charge
1 202-12-7831 Scott Spacey Fairfax VA 22030 (703) 432-4233 $170.00
2 301-20-3451 Randy Langston Fairfax VA 22030 (703) 502-3421 $180.00
3 190-18-1775 Mike Quinn Oakton VA 22180 (703) 265-9087 $220.00
Table: PaymentOption
PaymentOptionID PaymentDepositPct
PD05 0.05
PD10 0.10
PD15 0.15
PD20 0.20
PD30 0.30
PD40 0.40
PD50 0.50
PD60 0.60
Data for the Contract table should be imported from the Excel file Contract.xlsx.
MIS
303 –
to Business Information Systems: Access Tutorial
Page 1 of 42
Decision Making Using Access 2016 – I
Introduction
After the conceptual model (i.e., E-R diagram) of a database has been developed, the next
step is to convert the conceptual model into a physical model using a database software.
In other words, you are going to create a tangible database that will be used in practice. In
this tutorial, you will learn the basic skills of creating a database based on the developed
conceptual model using Microsoft Access 2016, including:
T1. Create a Blank Access Database
1. Launch Microsoft Access 2016, choose Blank database to create a database from
scratch.
2. In the pop-up window: (1) specify the name of the database; (2) click the folder icon to
specify where the database will be saved on your computer; (3) click Create to finish
creating the blank database.
Note:
.accdb is the file extension for
Access database file since
Microsoft Access 2007, before
which .mdb was used as the
file extension.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 2 of 42
T2. Add Tables and Set up Attributes for Tables
T2.1 Add and Set Up the First Table – Helicopter Table
1. You see that by default an empty table temporarily named as “Table 1” has been created.
Click the save icon to save the table and rename it.
2. In the pop-up Save As window, enter Helicopter as the name for the table as we are
creating the Helicopter table first. Then click OK.
3. For now, the Helicopter table is empty. Click View in the toolbar panel to get to the
design view of table where the attributes of a table are established.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 3 of 42
The attributes setup area consists of two panels. The top panel is used for specifiying all
the attributes (fields). The bottom panel is used for specifying the properties of each
attribute (field)
By default, the first attribute is created, with ID as the Field Name, AutoNumber as the
Data Type, and an empty Description (Optional).
Following the sequence of attributes for Helicopter entity in the E-R diagram, let us add
each of the attributes into the Helicopter table.
T2.1.1 Add and Set up HelicopterID Attribute
1. Enter HelicopterID to replace the default ID in the first row of attributes. Choose
AutoNumber as the Data Type (the default). When a field’s data type is AutoNumber, it
means that its value will be a unique number automatically generated by the Access 2016
software. Since its value is unique, an AutoNumber field is a suitable choice for the
primary key of a table.
Note:
• Attribute is also called Field or Column.
• Data Type specifies what values a Field can take. Even though it is not
considered in the conceptual model design stage, it is required in this
physical model design stage. This is one difference between the conceptual
model and the physical model.
• Description (Optional) specifies explanations of what a field means. Though
marked as optional, it is recommended that each field has a clear
description so that it is easier for any reader/programmer to understand the
meaning of a field in a database.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 4 of 42
2. The default Field Size for AutoNumber is Long Integer, covering integers that range
from -2,147,483,648 to +2,147,483,647. So an AutoNumber field can take a very wide
range of integer values. The New Values property specifies whether the field takes the
value of an incremental numeric counter from 1 to 2 to 3, etc. (Increment by default) or a
random integer value (Random). The usage of Random is rare unless you want to prevent
a possible damaging situation where others can guess the value of the next row from the
value of the current row for an AutoNumber field. In most cases, Increment is used.
In our case, HelicopterID takes the value of 1, 2, 3, 4, etc and it is the primary key of the
Helicopter table. Therefore, we set up HelicopterID as an Increment AutoNumber.
3. Enter a description for HelicopterID to explain its meaning. The finished setup for
HelicopterID is shown below:
T2.1.2 Add and Set up HelicopterName Attribute
1. In the second row of fields, type in HelicopterName. Since the name of a helicopter is
going to be text (rather than a number or a date, etc.) and it is not too long based on the
data provided, we choose Short Text as the Data Type for HelicopterName.
Note:
Short Text: up to 255 characters
Long Text: up to about 1 gigabyte (GB), but only display the first 64,000 characters.
Note:
• You can’t change the value of an AutoNumber field as it is automatically
generated by the Access software. Therefore, it is often used for a field that is
guaranteed to have unique values but its value has no meaning except for
being unique — the primary key.
• Each table is only allowed to have a maximum of one AutoNumber field.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 5 of 42
2. The default Field Size for a Short Text is 255, which specifies that the the maximum field
value size is 255 characters. But we know the name of a helicopter is typically not too long
and let us assume that Joe’s SkyTour Inc. has a business rule that any helicopter name
should be between 1 and 30 characters and that it is required for each helicpter. To capture
this business rule, we need to: (1) change the Field Size from the default 255 to 30; (2) Set
Required to be Yes as its value is required; (3) Set Allow Zero Length to be No.
3. Add a description to explain what HelicopterName means as an attribute. The finished
setup for HelicopterID is shown below:
T2.1.3 Add and Set up HelicopterModel Attribute
Similar to HelicopterName, the Data Type for HelicopterModel should be Short Text.
Assume that Joe’s SkyTour Inc. has a business rule that the length of any helicopter model
should be between 1 and 20 characters and that it is required for each helicopter. The
finished setup for HelicopterModel is shown below:
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 6 of 42
T2.1.4 Add and Set up SeatingCap Attribute
1. In the fourth row of fields, type in SeatingCap. Since the seating capacity of a helicopter
is a number and its value is not too big based on the data provided, we choose Number as
the Data Type for SeatingCap.
2. Since the seating capacity of a helicopter is going to be an integer and in our cases its
value is less than 255, we choose the Field Size to be Byte.
Note:
Byte: integers that range from 0 to 255.
Integer: integers that range from -32,768 to 32,767.
Long Integer: integers that range from -2,147,483,648 to 2,147,483,647.
————————————————————————————————————-
Single: floating point values that range from -3.4 x 1038 to 3.4 x 1038.
Double: floating point values that range from -1.797 x 10308 to 1.797 x 10308.
Decimal: floating point values that range from -9.999… x 1027 to 9.999… x 1027.
————————————————————————————————————-
Replication ID: a globally unique identifier required for replication.
Note:
Number: take values from -2,147,473,648 to 2,147,473,647.
Large Number: take values from -9.22337204e+18 to 9.22337204e+18.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 7 of 42
3. The Default Value for a number is by default 0. Remove it so that there is no Default
Value.
4. Set Required to be Yes as its value is required. Set Allow Zero Length to be No. Also add
a description for SeatingCap. The finished setup for SeatingCap is shown below:
T2.1.5 Add and Set up HourlyCost Attribute
1. In the fifth row of fields, type in HourlyCost. Since the hourly cost of a helicopter is a
currency, we choose Currency as the Data Type. Add a meaning description.
2. In addition: (1) set up the Decimal Places to be 2 to be consistent with the data; (2)
remove Default Value; (3) set up Required to be Yes; (4) add a meaningful description.
Note:
When a field has a Default Value, its value for every single record will be the Default
Value unless the user gives it another value or modifies it. Setting up Default Value
can be very useful in terms of saving data entry time and effort when the value of a
field does not change much. Unless there is a specific business rule regarding the
by-default value of a field or you have domain knowledge about how often the field
value changes, you should remove Default Value.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 8 of 42
T2.1.6 Add and Set up DateLastMaint Attribute
1. In the next row of fields, type in DateLastMaint. Since this attribute is a date, we choose
Date/Time as the Data Type. There are different Formats for a Date/Time field, we choose
the correct one among all the listed formats. In our case, it should be Short Date based on
the data provided.
2. To make sure that data for this field is entered correctly, we can set up an input mask for
DateLastMaint such that the data for this field is always entered in the pre-specified short
date format. Click on the cell right to Input Mask, and then click the … button. In the
pop-up Input Mask Wizard window, click Yes.
3. Then the following Input Mask Wizard window will show up. Choose Short Date
9/27/1969 among all the listed input masks, and then click Finish (no need to click Next
since we will keep the default setting in subsequent steps).
You will notice that the input mask cell has been replaced with a pattern of strings.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 9 of 42
4. In addition, set up Required to be Yes assuming that its value is required, and add a
meaningful description. The finished setup is shown below:
T2.1.7 Set up the Primary Key
For each table, we need to specify a primary key. In Access, the primary key is denoted by
the key icon to the left of the Field Name column.
By default, the first field is assigned as the primary key. In our case, HelicopterID is
automatically assigned as the primary key and we do not need to change it.
No assume that we want to assign HelicopterName (which is unique for each helicopter) as
the primary key. Move the mouse over the row selector area (to the left of Field Name
column). Once the right arrow appears, click the mouse to select the HelicopterName row.
The helicopterName row is now selected:
Under Design menu, click the Primary Key button to set/unset primary key.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 10 of 42
Now HelicopterName is set as the primary key.
Now change back so that HelicopterID is the primary key.
Remember to click the save icon to save what you have done.
Note:
The Primary Key button under Design menu allows setting and unsetting primary
keys.
• If a field is already set as the primary key, clicking the Primary Key button
will unset it.
• If a a field is not set as the primary key, clicking the Primary Key button will
set it as the primary key AND override the old primary key by unsetting the
field which was set as the primary key. Why? Remember that each table
has one and only one primary key.
To set up a composite primary key, you need to simultaneously select the rows of
the multiple fields that together serve as the primary key first by holding down
the CTRL key. Then click the Primary Key button to specify that the multiple fields
are used together as a composite primary key.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 11 of 42
T2.2 Add and Set up the Second Table – TourType Table
1. Click Create menu, and then click Table to add a new table.
2. Click the save icon to save this new table. You will be prompted to enter a name for the
new table. In the pop-up Save As window, enter TourType and then click OK.
3. We have created an empty TourType table. Now we need to get to the design view of
the table to set up attributes for the table. In addition to clicking on the View button, there
are two other ways to get to the design view as shown below.
(1) Click the View button in
the toolbar panel.
(2) Right click on the table
tab (i.e., TourType in this
case), and click Design View
in the drop-down menu list.
(3) Right click on the table
name under Tables panel,
and click Design View in the
drop-down menu list.
T2.2.1 Add and Set up TourType Attribute
1. For TourType, the Data Type should be Short Text as it takes string values such as “S”,
“M”, and “L”.
2. In terms of properties of this Short Text, the Field Size should be 1. In addition, set up
Required to be Yes, and Allow Zero Length to be No.
3. Do not forget to add a meaningful description for the attribute.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 12 of 42
T2.2.2 Add and Set up TourTypeName Attribute
1. For TourTypeName, the Data Type should be Short Text as its text length will not be too
long, at least based on the data provided. Assume that Joe’s SkyTour Inc. has a business
rule that any tour type name should be between 1 and 30 characters and that it is required
for each tour type.
2. In terms of properties of this Short Text, the Field Size should be 30. In addition, set up
Required to be Yes, and Allow Zero Length to be No.
3. Again, remember to add a meaningful description for the attribute.
T2.2.3 Add and Set up ExpectedHours Attribute
1. For ExpectedHours, the Data Type should be Number as it is a number.
2. Based on the data provided, it is a floating point number with 2 decimal places and its
value is typically small. Therefore, the Field Size should be Single, and Decimal Places
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 13 of 42
should be 2. In addition, remove Default Value, and set up Required to be Yes.
3. Finally, add a meaningful description for this attribute.
T2.2.4 Add and Set up TicketPrice Attribute
1. For T icketPrice, Currency should be the Data Type and its Decimal Places should be 2
based on the data
provided.
2. In addition, remove Default Value, set up Required to be Yes, and add a meaningful
description.
T2.2.5 Set up the Primary Key
By default, the first attribute TourTypeID is assigned as the primary key. And this is exactly
what we want. Do not change it and we can move to the next table. Remember to click the
save icon to save what you have done.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 14 of 42
T2.3 Add and Set up the Third Table – Pilot Table
1. For the Pilot table, we try a slightly different way. Let us add the Field Name, the Data
Type, and Description for all attributes first. Then we will set up the properties for each
attribute’s Data Type.
2. The Data Type for PilotID should be Short Text. It takes the value of letter P followed by
three digits. Therefore, we can set up the Field Size to be 4. It is the primary key. Set
Required to be Yes and Allow Zero Length to be No since it should take a value as the
primary key.
Note: Input mask includes one mandatory component followed by two optional components,
with a semicolon between two adjacent components.
• The first component is mandatory. It includes the mask characters or strings along with
placeholder characters and literal data such as parentheses, hyphens, and periods.
• The second component is optional. It is set to be either 0 or 1: 0 means that the mask
characters are stored with the data; 1 is the default value meaning that the mask
characters will not be stored though displayed (i.e., only what you typed in for the
placeholder characters is stored).
• The third component is optional. It indicates how the placeholder will be displayed for data
entry. You may set up your own placeholder or use the default placeholder: _.
The table below lists some placeholder characters commonly used for an input mask:
Placeholder
Character
Explanation
0 A digit from 0 to 9 must be entered
9 A digit from 0 to 9 can be entered (optional)
L A letter must be entered
? A letter can be entered (optional)
A A digit or letter must be entered
a A digit or letter can be entered (optional)
. , : ; – / Decimal and thousands placeholders, date and time separators, dependent on
your regional settings.
\ The character immediately after it will be literally displayed. E.g., L will be
treated as representing any letter, but \L will be treated literally as L; similarly, –
will be treated as a date and time separator used for date or time inputs, but \-
will be literally treated as a hyphen.
(Optional) Since we know that the value of PilotID will be P plus three digits, we can set up
the input mask so that its value will be entered in this pre-specified format. The Input Mask
for PilotID (if specified) should be: \P000;0;_
The first component \P000 specifies that letter P will be displayed and users will need to enter
three digits; the second component 0 specifies that the letter P will be stored along with the
three digits; the third component _ specifies that underscore will be used as the placeholder.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 15 of 42
3. Obviously at first glance, the Data Type for all the attributes related to name and
address, including FirstName, LastName, Street, City, State should be Short Text. For these
attributes, without clearly specified business rules, we set up the appropriate Field Size by
domain knowledge. For example, we know that FirstName and LastName would typically
not exceed 30 characters, so we set up its Field Size to be 30. We assign a slightly larger
Field Size 50 for Street and a slightly shorter Field Size 20 for City. For attribute State, from
the data provided, we know that it is using the abbreviated 2-letter format. Therefore, its
Field Size should be 2. For all of them, set Required to be Yes and Allow Zero Length to be
No assuming that they should not be left empty.
FirstName LastName Street City State
4. Even though SSN is composed of 9 digits, it is inappropriate to use Number for its Data
Type for two major reasons. First, as seen from the data provided, the value of SSN also
includes two hyphens. If these two hyphens are counted, the Data Type for SSN should be
Short Text. Second, a Number field indicates that the field has a mathematical
connotation (e.g., you can compare one value against another value; you do mathematical
operations such as adding up two values or subtract one value from another value, etc.),
which does not apply to SSN. Therefore, the correct Data Type for SSN should be Short
Text rather than Number.
The Field Size should be 11, counting 9 digits and 2 hyphens. Set Required to be Yes and
Allow Zero Length to be No. We can also set up the embedded SSN input mask using the
input mask wizard. Click Next to follow the Input Mask Wizard, choose “With the symbols
in the mask, like this:” so that the symbols such as hyphens are also stored in the value of
the data.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 16 of 42
5. Similarly, the correct Data Type for ZipCode should be Short Text rather than Number
given that ZipCode has no mathematical connotations.
The Field Size should be 5. Set Required to be Yes and Allow Zero Length to be No.
6. The Data Type for Phone should be Short Text rather than Number.
The Field Size should be 14, counting all numbers, parentheses, hyphens and space. Set
Required to be Yes and Allow Zero Length to be No. We can also set up the embedded
Phone Number input mask using the input mask wizard. Choose “With the symbols in the
mask, like this:” so that the symbols such as hyphens are also stored in the value of the
data.
Self-Exercise:
Why should the Data Type for Phone be Short Text rather than Number?
Self-Exercise (Optional):
The value of ZipCode takes the form of 5 digits. Set up the input mask for
ZipCode so that users can only enter 5 digits as its value.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 17 of 42
7. The Data Type for DateofBirth should be Date/Time.
Similar to DateLastMaint, set up the Format to be Short Date, and add a Short Date Input
Mask for it.
Set Required to be No as it is not required as seen in the data provided.
8. Remember to click the save icon to save what you have done.
T2.4 Add and Set up the Fourth Table – Trip Table
1. As seen from the data provided, the value of TripID is incremental integer taking values
from 1 to 2 to 3, etc. It is also the primary key. Therefore, the Data Type for TripID should
be AutoNumber.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 18 of 42
2. HelicopterID, PilotID, and TourTypeID are foreign keys that map to the primary key
HelicopterID in the Helicopter table, the primary key PilotID in the PilotTable, and the
primary key TourTypeID in TourType table respectively. Therefore:
• PilotID here should be the same as PilotID in the PiotTable: the Data Type should be
Short Text with a of Field Size 4. Implied from the E-R diagram and the data
provided and, it is a required attribute. Therefore, set Required to be Yes and Allow
Zero Length to be No.
• TourTypeID here should be the same as TourTypeID in the TourType table: the Data
Type should be Short Text with a of Field Size 1. Implied from the E-R diagram and
the data provided, it is a required attribute. Therefore, set Required to be Yes and
Allow Zero Length to be No.
• HelicopterID here should be the same as HelicopterID in the Helicopter table.
However, because AutoNumber has already been used for TripID and each table
can only have one AutoNumber attribute, we could not set up HelicopterID to be an
AutoNumber. Also, for each trip, its associated HelicopterID value should not be
automatically generated by the software. Instead, it should be the ID of the
helicopter actually used for the trip. Therefore, do not use AutoNumber for
HelicopterID. Because HelicopterID takes the value of an integer, we set up its Data
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 19 of 42
Type to be Number. In addition, as a foreign key, it maps to HelicopterID in the
Helicopter table where it is a Long Integer. Therefore, the Field Size should be the
same Long Integer. Remove Default Value and set Required to be Yes.
3. For TripDate, obviously the Data Type for should be Date/Time.
Similarly, set up the Format to be Short Date, and add a Short Date Input Mask using the
Input Mask Wizard for it. Set Required to be Yes as it is required as seen in the data
provided.
4. For StartTime, obviously the Data Type for should be Date/Time.
The Format should be Medium Time.
Add a Medium Time Input Mask using the Input Mask Wizard for it. Set Required to be
Yes as it is required as seen in the data provided.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 20 of 42
5. For NumPeople, the Data Type for should be Number.
Because it takes small integer values, its Field Size should be the Byte. Remove Default
Value and set Required to be Yes.
6. Remember to click the save icon to save what you have done.
T3. Establish Relationships between Tables
Now after we have created all the tables, we need to establish the relationships between
the tables.
T3.1 First Make Sure Tables are Closed
It is important that no table is open or being used before establishing relationships. Close
all tables using either of the two ways:
(1) Right click on any table tap, choose Close
All in the drop-down menu list.
(2) Click the X button for all table tabs.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 21 of 42
Your window should look like below with no tables being opened:
T3.2 Add Tables to the Relationships Layout View
1. Click Database Tools menu, and then click Relationships to start adding tables for
creating relationships.
2. A Select Table window will pop-up. Select each table listed, and click the Add button to
add the table to the relationships layout view. Once all tables have been added, click Close
button.
Note:
If any table (e.g., the Trip table) is open, later when establishing relationships, you
will get the following error message:
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 22 of 42
After all tables have been added, you should see:
You can arrange the position of a table by keep pressing on the table name and dragging it
around.
T3.3 Add Relationships and Enforce Referential Integrity
Let us create the first relationship between Helicopter and Trip. Remember that a
relationship is created by linking a foreign key in one table to its corresponding primary
key in another table. In this case, we should link HelicopterID in the Trip table (i.e., foreign
key) to its corresponding primary key – HelicopterID in the Helicopter table to create the
relationship.
Note:
A quicker way to add a table is to Double Click each
table.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 23 of 42
1. Click HelicopterID in the
Helicopter table, keep your mouse
pressed, and drag it until it reaches
the HelicopterID in the Trip table.
Then release your mouse.
2. In the pop-up Edit Relationships
window, check Enforce Referential
Integrity. Then click Create.
Remember that referential integrity
helps ensure and consistency and
integrity of data among tables. Later
when entering or editing data, you
will notice that some error
messages will be thrown if data
inconsistency is discovered by
Access.
3. The relationship between Helicopter and Trip has been establish as shown below:
Note:
In advanced database courses, you will learn that a many-to-many relationship needs
to be transformed into two one-to-many relationships before creating the physical
design of the database using Access or other database software.
Note:
The cardinality numbers (1, and ∞
which means many) will only be
shown if Enforce Referential Integrity
has been checked. If they are not
shown, it is an indication that Enforce
Referential Integrity is not checked.
Note: For one-to-many relationships, the
sequence does not matter: you may link
from a foreign key to a primary key or link
from a primary key to a foreign key. For
one-to-one relationship, drag from the
primary key to the foreign key.
Note:
The foreign key and the primary key to establish a relationship are not necessarily
named the same. For example, even if the foreign key is named TripHelicopterID
instead of HelicopterID in the Trip table, you should still link it with HelicopterID in
the Helicopter table because they are both about the ID of the helicopter and their
values should match.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 24 of 42
4. Create the rest of the relationships and your finished Relationships layout view should
look like below. Remember to click the save icon to save what you have done.
T3.4 Fix Wrong Relationships If Necessary
If you created a wrong relationship and want to fix it, you can delete the wrong
relationship first and then add the correct relationship.
Note:
The Data Type of a foreign key should match the Data Type of its corresponding
primary key as much as possible to be able to establish a relationship. If the
following error messages shows up, it means that the Data Types of the foreign
key and the primary key do not match and you need to go to the design view of
the table to fix it.
The above error message could be caused by:
• HelicopterID in Helicopter table is Long Integer while HelicopterID in Trip
table is Integer.
• PilotID in Pilot table is Short Text while PilotID in Trip table is Number or
AutoNumber.
• …
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 25 of 42
To delete a relationship, click on the line representing the relationship to select it first. A
selected relationship will be highlighted in bold. Then right click, and choose Delete from
the pop-up menu list. Then click Yes in the pop-up prompt window to confirm the delete
operation.
T4. Add Data into the Database
After we have converted the E-R diagram into a full-fledged physical model, we now focus
on adding sample data into the database.
There are multiple ways to enter the data for a table. We will cover each of them with
examples.
T4.1 Use the DataSheet View to Enter Data
1. The DataSheet view is similar to an Excel Worksheet where you directly enter the data
for each row and column. Double click on each table in the Tables panel to enter the
Note:
In the case of multiple tables (e.g., four tables in this exercise), the order of tables
for data entry is very important due to enforced referential integrity which requires
that a foreign key value (if required) must match the value of its corresponding
primary key value in another table or equals NULL (if not required). Therefore,
before we enter data for a table with foreign keys, we need to make sure that we
have entered the data for the table(s) where the foreign keys’ corresponding
primary keys exist.
In this exercise, the Trip table has foreign keys such as HelicopterID, PilotID and
TourTypeID and their values must match the possible values in other three tables.
Therefore, we need to enter the data for tables Helicopter, Pilot, and TourType
first before entering the data for Trip table.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 26 of 42
DataSheet view of the table.
During the data entry process, the Input Mask will be shown. For example, the Short Date
Input Mask for DateLastMaint will be effective and you only need to enter the digits for
month, day, and year.
The values for HelicopterID are automatically filled in as 1, 2, 3, etc. If you forget to enter
the value for a field whose value is required, you will get an error message when clicking
the save icon to save what you have done.
The finished Helicopter table with data will look like below. Remember to click the save
icon to save what you have done.
Note:
The cell for HelicopterID is marked with (New). This is because it is set up as an
AutoNumber and its value will be automatically generated by the software. Therefore,
we do not have control over it and we do not enter data for it. We directly enter the
data for other fields.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 27 of 42
Again, during the data entry for the Pilot table using the DataSheet view, the following
Input Masks that were set up earlier will be shown.
(if it was set
up)
(if it was set
up)
2. The finished Pilot table with data will look like below. Remember to click the save icon
to save what you have done.
T4.1.1 Solve the Non-Continuous Problem for AutoNumber
When a field’s Data Type is an Increment AutoNumber, its value is automatically
generated by the software and you do not have control over it. If you accidentally remove
one row, the automatically generated number that has been deleted would not come back.
As a result, when the values of an AutoNumber field are supposed to be continuous
integers like 1, 2, 3, 4, etc., the actuals values shown on your table might be
non-continuous like 1,2 3, 5 (if you have deleted 4) or 1, 3, 4, 5 (if you have deleted 2). This
section is dedicated to solving this problem and making AutoNumber continuous again.
Suppose you have accidently removed Helicopter 4: right click on the row selection area,
choose Delete Record from the pop-up menu list, and click Yes in the pop-up prompt
window.
Now as you try to add Helicopter 4, you will notice that HelicopterID 4 is not coming back.
The newly created HelicopterID is 5.
Note:
Because the Required property for DateofBirth is set to No, it is allowed to not take a
value.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 28 of 42
To solve the problem and make HelicopterID continuous again, you need to remove the old
HelicopterID and create a new HelicopterID that is an AutoNumber. In this way, Access will
start creating AutoNumbers from square one and the values of HelicopterID will be
continuous again.
1. Because HelicopterID is used in one relationship, we have remove the relationship first
before we can remove HelicopterID from the Helicopter table.
Before we make changes to the relationships, remember that we have to close all tables
first so that they are not being used.
Then go the Relationships layout view and remove the relationship involving HelicopterID:
right click on the line and click Delete.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 29 of 42
The new Relationships layout view should look like this:
Then click the save icon to save what you have done, and exit the Relationships layout
view by clicking the Close buttoin in the toolbar panel.
2. Go the design view of the table you want to fix (in this case Helicopter table).
Because each table is required to have a primary key, we need to unset HelicopterID to
make it not a primary key and rename it (e.g, to HelicopterID1) so that we could add a new
primary key named HelicopterID before we remove the old HelicopterID.
Note:
If multiple tables with AutoNumber attributes have the non-continuous problem that
needs to be fixed, you should remove all the relationships involving the multiple
tables.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 30 of 42
3. Now we need to add a new attribute named HelicopterID to be used as the new primary
key. Right click on the row selector area for HelicopterID1, click Insert Rows.
4. Add the new attribute HelicopterID in the newly added row at the top. Make it
AutoNumber and add the same description as HelicopterID1. Then make HelicopterID as
the Primary Key by clicking the Primary Key button to make it selected.
5. Now remove the old HelicopterID which is renamed as HelicopterID1. Right click on the
row selector area and click Delete Rows. In the pop-up prompt window(s), click Yes.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 31 of 42
6. Click the save icon to save what you have done. Then double click on Helicopter table in
the Tables Panel to open its DataSheet view. Now the values of HelicoperID are
continuous.
7. Now you need to add the removed relationship(s) back. This is very important and you
should not forget about it. Afterwards, click the save icon to save what you have done.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 32 of 42
T4.2 Use Form to Enter Data
1. Now we will create a form for data entry into the TourType table. Under Create menu,
click Form Wizard button.
2. In the pop-up Form Wizard window, follow the step-by-step instructions to create the
form.
(1) Select the table for which the form will be created – the TourType table.
Then select what attributes of the selected table will be shown in the form. The > button
allows you to add one selected attribute and the >> button will add all attributes at once.
Click the >> button so that all attributes will be shown in the form for data entry. Then click
Next.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 33 of 42
(2) Choose a layout for the form. We can go with the by-default columnar layout. Click
Next.
(3) Enter a meaningful title for the form. Then click Finish.
(4) The form has been created and can be used to enter data.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 34 of 42
For example, we can add the first tour type and then click the next arrow to save it.
(5) We can add our own navigation buttons that are bigger and more intuitive for
navigation. Right click on TourType Data Entry Form in the Forms panel, choose Design
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 35 of 42
View.
3. We will add three buttons – one button for moving to the next record, one button for
moving to the previous record and one button for saving the current record – to the right
of the textboxes.
(1) First, widen the form a little bit to allow spaces for the three to-be-added buttons.
Move the mouse over the right border of the form, when an arrowed cross appears, keep
the mouse pressed and drag it to widen the form.
Before Widened:
After Widened:
(2) Now add the first button for moving to the previous record. Under Design menu, click
the Button icon in the toolbar.
(3) Then click on the form at where you want to add the button.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 36 of 42
(4) In the pop-up Command Button Wizard window for setting up the button, Select
Record Navigation under Categories and Go To Previous Record under Actions. Then click
Next.
(5) We can choose Text (by default “Previous Record”) style for the button. Then click Next.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 37 of 42
(6) We then give the button a meaning name and click Finish.
The button for nagivating to the previous record has been added.
(7) Similarly, we can add the button for navigating to the next record and for saving a
record.
Next Record Button: you can
adjust the size and position of
the button.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 38 of 42
Save Record Button: you can
adjust the size and position of
the button.
3. Now double click on TourType Data Entry Form in the Forms panel to use it.
4. After you have added all the records, double click the TourType table on the Tables
panel to open its DataSheet view. You will notice that the added records are there.
Note:
Sometimes, the data is not shown in the DataSheet view after you have added them
through the form. This is ONLY because the DataSheet view has not been refreshed.
Close the DataSheet view first and then re-open it to refresh. You may need to do it a
couple of times before the data is shown in the DataSheet view.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 39 of 42
T4.3 Import Data to a Table
1. You can also important data from external sources to a table. Suppose we want to
import data from an Excel spreadsheet to the Trip table. Right click on Trip table in the
Tables panel, click Import and then choose Excel in the pop-up menu list. A Get External
Data window will pop-up.
2. In the pop-up Get External Data window, first click the Browse… button to select the
Excel file (Trip.xlsx) with the data to be imported. Then select Appends a copy of the
records to the table, and select Trip table from all the listed tables. Then click OK.
3. The next Import Spreadsheet Wizard window provides a view of the data from the
external Excel file. Click Next.
Note:
If the Excel file contains multiple worksheets, you will be prompted to select which
worksheet will be imported.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 40 of 42
4. Next, confirm the data will be imported to the Trip table. Then click Finish.
5. In the next window, click Close. Then double click the Trip table on the Tables panel to
open its DataSheet view. You will notice that the imported records are shown.
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 41 of 42
6. Always remember to click the save icon to save what you have done.
T5. Test Referential Integrity
For the opened Trip table, if we revise the HelicopterID for Trip 10 to be 5. When you click
the save icon to save what you did, you will get an error message. This is because of
referential integrity: there is no Helicopter with HelicopterID 5 in the Helicopter table to
match the HelicopterID 5 here. No problem will occur if changing the value back to 1 and
saving it.
Now open the DataSheet view of the Pilot table and try to remove the pilot record with
PilotID P002.
Note:
If you get an error message when importing data, check these common possible
problems first:
• Are the attributes names consistent with the column labels in the Excel file?
• Have you set up the Required property of each attribute correctly? For
example, if it is set up to be Yes for an attribute, but in the Excel file the
value of the attribute is empty in some rows. Then you would have an
inconsistency issue, leading to an error message.
• Have you set up the Field Size property of each attribute correctly? For
example, you would have an error message if the field size of an attribute is
set up to be 3 but the length of the attribute values in Excel is more than 3.
• Have you set up the Input Mask property of each attribute correctly? For
example, you would have an error message if the value of the attribute in
Excel is inconsistent with the specified input mask for the attribute.
• Is referential integrity violated when there are foreign keys? Make sure that
every foreign key value should find a match in the primary key value.
If none of the above problems exist, sometimes you simply need to replicate the
import process a few times to have all the records imported (it is an Access glitch).
MIS 303 – Introduction to Business Information Systems: Access Tutorial
Page 42 of 42
An error message will pop up due to referential integrity: if this pilot were removed, all the
records in the trip table with PilotID P002 would not be able to find a match in the Pilot
table.
A Final Note:
Whenever an Access file is opened, a temporary lock file with the file name
extension .laccdb (not .accdb) will be created. This lock file is used to prevent
different users from makings changes to the database at the same time.
The temporary lock file is different in two ways:
• The file icon is different with a lock
• The file extension is different: .laccdb if you allow file extensions to be
shown.
After you have closed the database file and exited Microsoft Access program, the
temporary lock file will be automatically deleted and disappear. The temporary
lock file is NOT the actual database file and could be not opened. To avoid
making the mistake of transferring the wrong lock file when you plan to transfer
the actual database file, it is highly recommended that you close all Access files
and program first so that you will only see the actual database files.
- Introduction
T1. Create a Blank Access Database
T2. Add Tables and Set up Attributes for Tables
T2.1 Add and Set Up the First Table – Helicopter Table
T2.1.1 Add and Set up HelicopterID Attribute
T2.1.2 Add and Set up HelicopterName Attribute
T2.1.3 Add and Set up HelicopterModel Attribute
T2.1.4 Add and Set up SeatingCap Attribute
T2.1.5 Add and Set up HourlyCost Attribute
T2.1.6 Add and Set up DateLastMaint Attribute
T2.1.7 Set up the Primary Key
T2.2 Add and Set up the Second Table – TourType Table
T2.2.1 Add and Set up TourType Attribute
T2.2.2 Add and Set up TourTypeName Attribute
T2.2.3 Add and Set up ExpectedHours Attribute
T2.2.4 Add and Set up TicketPrice Attribute
T2.2.5 Set up the Primary Key
T2.3 Add and Set up the Third Table – Pilot Table
T2.4 Add and Set up the Fourth Table – Trip Table
T3. Establish Relationships between Tables
T3.1 First Make Sure Tables are Closed
T3.2 Add Tables to the Relationships Layout View
T3.3 Add Relationships and Enforce Referential Integrity
T3.4 Fix Wrong Relationships If Necessary
T4. Add Data into the Database
T4.1 Use the DataSheet View to Enter Data
T4.1.1 Solve the Non-Continuous Problem for AutoNumber
T4.2 Use Form to Enter Data
T4.3 Import Data to a Table
T5. Test Referential Integrity