Database Exercise with MS Access

Database Exercise with MS Access

Prepared by Windows User

Enter your Student ID in the Properties of this document

Prepared for Nelson Cartagena

CMIS2250 Section OA08 (Term 1202)

Date of Submission: Wednesday, August 28, 2024

Preamble

In this assignment you will be using either text files (.txt) Excel files (.xlsx), or MS Access files (.accdb) to answer the following questions. Download the files from your Moodle course. Whenever necessary, use MS Access. Screenshots may be required.

Photos taken with your smart/mobile phones DO NOT count as screenshots.

You need to use the PrintScrn button or a combination of key strokes to get a “grab” of your screen. PC users can use the built-in application in Windows: “Snipping Tool”, Mac users can press COMMAND + SHIFT + 4 to start grabbing a piece of, or the entire screen. See below for a sample (blank) screenshot.

Your Access objects (Tables, Queries, etc.) ought to be saved with your surname embedded into them. So, for example, a table must be named TBL_Smith, if your last name were Smith. Follow that rule for each object you save and as requested below.

Change the properties of this Word document to match your credentials as Author, and edit the Keywords as your NAIT Student ID. Your name will automatically appear on each header of a page of this document.

Submit a .pdf version of this document.

Figure 1: Sample Screenshot of Design View of a MS Access Query.

QUESTION LIST

Question 1 – Importing a File into MS Access

Do the following: Import the “HRData1202.xlsx” file into a MS Access of your choice (blank). Name the Table object as TBL1_YourLastName (where “YourLastName” is your actual Surname as recorded in Moodle). Do not import the “Password” data. Take a screenshot of the Design View of the Table object and include it in your answer below. Filter the records by the oldest Staff member in the database. Take a screenshot of the first 10 records in Datasheet View of your table and paste it below.

What is the proportion (show % with two decimals) of personnel who joined the company in the last two years? ___________ %

[10 marks]

The proportion (show % with two decimals) of personnel who joined the company in the last two years 17.00 %

Question 2 – Parameter Query

Do the following, using the Clients_CMIS2250_2021_1202_2.accdb file provided: Delete the table Customers 2021 v1. Using the Customers 2021 v2 table, create and save a parameter query that will prompt the user for the two-letter Province (PoR) and result in the listing of the following: First Name, Last Name, City, PoR, Employment Status, and Mortgage for only the records that do have an outstanding Mortgage Balance of more than $100,000.00. Save your query as QRY2_YourLastName. Take a screenshot of the Design View screen of your query and paste it below. [15 marks]

Question 3 – Calculated Query

Continue using the database from Question 2 and the same table. Do the following: Create and save a select query that will display the First Name, Last Name, City, PoR, and Monthly Income for each of the individuals who earn less than $250,000.00, who reside in Ontario (ON), Alberta (AB), and Québec (QC) and for whom we do not have a marital status declared. Show results of the monthly income with two decimals only. Save your query as QRY3_YourLastName. Take a screenshot of the Design View screen of your query and paste it below. [15 marks]

Question 4 – Select Query, Criteria , Calculated field with date/time Data type and Sorting key(s)

Continue using the database from Question 2 and the same table. Do the following: Create and save a query that will display the First Name, Last Name, Age, Car Brand, Car Model and Year of those customers who live in Manitoba and Québec and whose last transaction has exceeded $95,00. Results should show Québec residents first. Save your query as QRY4_YourLastName. Take a screenshot of the Design View screen of your query and paste it below. [10 marks]

Question 5 – Select Query, Wildcard, Calculated field with Time/Date

Continue using the database from Question 2 and the same table. Do the following: Create and save a query that will display the First Name, Last Name, Age, Birthdate of a person who’s name begins with a letter identical to your initial in your surname. Save your query as QRY5_YourLastName. Take a screenshot of the Design View screen of your query and paste it below. Take a screenshot of the SQL View of your query and paste it below. [5 marks]