Management Information Systems


Management Information Systems
Assignment III
Question 1
1. Create a blank database, save it on your desktop and change the file name to [your
2. Create a new table and save it as tblQuestion_I.
3. In design view for tblQuestionI, enter the following fields:
Field Name Date Type Description
StudentID AutoNumer Student identification number
LastName Text Last name of student
FirstName Text First name of student
PhoneNum Text Student’s phone number
4. Save the changes to the design of the table.
5. Input the following records in tblQuestion_I.
6. Create a query based on tblQuestion_I. Save it as qryQuestion_I. Add the following fields to this
query: StudentID, LastName, FirstName, PhoneNum.
7. Use a single criterion to show StudentID number 1 only.
8. Sort the tblQuestion_I by Last Name in ascending order.
9. Create a PDF file that shows only all the information in tblQuestion_I. Name the file
Question 2
1. Make a copy of the AccessData database and rename it [your initials]AccessData. Open [your
2. Using tblManagement, do the following:
a) Add a new field between the ZIP and PhoneNum fields with the following settings:
Name: CellNum
Format: Text
Field Size: 15
Caption: Cell Number
b) Change the ContractEnds field’s Format property to Short Date.
c) Hide the following columns:
Phone NumberNon Profit?
d) Add the following record:
Management ID: [AutoNumber]
Management Name: [Your school’s name]
State: NJ
Cell Number: 901-555-3548
ContactFirstName: [Your first name]
ContactLastName: [Your last name]
ContractEnds: [Today’s date]
e) Using the data found in the Excel file NewManagement, import the data into the table
f) Delete the record for Management ID 38.
g) Apply a filter to show only management companies located in Tennessee.
h) Sort the table by Management Name in ascending order.
i) Create a PDF file that shows only the management companies from Tennessee with the filter
and sort order above. Make sure that all data can be seen on one page in landscape orientation.
Name the file Question_II_PDF and submit it.
j) Save and close the table.
3. Using frmFacContacts, do the following:
a) Add the following new facility:
Facilities ID: [AutoNumber]
Facility Name: [Name of your class]
Contract First Name: [Your first name]
Last Name: [Your last name]
Phone Number: 931-555-6851
Region: 3
b) Change the phone number of William Zellefrow’s to 215-555-2000.
4. Using frmInventory, locate the record for Inventory ID 5, add the picture Circuline.jpg to this
record’s Image field.
5. Use tblVender to do the following:
a) Find all occurrences of “Ave.” and replace them with “Avenue.”
b) Hide the PhoneNum and FAX fields.
6. Save all changes. Submit and close [your initials]AccessData.
Question 3
1. Download EM-E2.accdb and rename it [your initials]-EM-E2.accdb.2. Open [your initials]-EM-E2.accdb.
3. Open tblFacilities in datasheet view. Notice that the fields Management, Facility Type and
Manage Type are numbers. Modify these fields to store a number but show a text value instead.
(Hint: use look up wizard to search the text values in related tables. For example, for field
Management, you need to look up to related fields in the table tblManagment.)
4. In the Relationship window, create relationship between tblManagement-tblFacilities,
tblManageType-tblFacilities, tblFacType- tblFacilities. Modify the three relationships to enforce
referential integrity.
5. Create a relationship report that only shows the tables tblFacilities, tblManagement, tblFacType,
tblManageType. Make sure that all fields and relationship are visible. Save the relationship report
as rptRel.
6. Create a query named qryFac-Lookups. Include the fields FacID, MgtID, FacTypeID,
MgtTypeID. Show only hospitals that are Long Term (FacTypeID = 3) facilities.
7. Create a query named qryMgtContacts. Add the name of the management companies. Next add
a field that includes the contact’s first names. The field’s caption should be Name. Add the
contact’s phone number.