Database Exercise with MS Access

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]

Shape2

Shape3

Shape4

Shape5

Shape6

Shape7

Shape8

Shape9

Shape10

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

Shape11