Microsoft Access Assignment Due in 23HR

Your Task A database for your hospital, Panther Memorial, has been started. So far, the tables have been built and some test data have been loaded. Your job is to add additional components to the database. Specifically, you will be building queries to answer questions for our management.

Deliverables You will deliver a Microsoft Access database that contains queries that answer each of the questions from management. The queries will be named in the following convention: Question# (meaning Question1 for #1 below, Question2 for #2 below, etc).

Starter Database When starting your project, download the Panther Memorial Starter Database from D2L and then add to this database. DO NOT modify the data in the database – otherwise your answers will not match the answer key and you will not receive credit.

Queries

1. Single table query with sorted fields:

                The Vice President wants to send out birthday cards to our provider.  Our VP is very picky and wants to the see the results with the following fields only: Job Title, Courtesy Title, First Name, Last Name, and Birth Date.  The query should be sorted by the job title in descending order and then birth date ascending order (that way her name is at the top).

Question 1
Job title	Title	First name	Last name	Date of birth
Vice President	Mrs.	Julie	Jones	1/9/1954
Physician	Miss	Rose	Radford	12/12/1956
Physician	Mr.	John	Butcher	8/9/1964
Midwife	Mrs.	Paula	Tree	7/1/1970
Midwife	Ms.	Ingrid	Ikemire	9/1/1971

 

2. Single table query with string concatenation, and an alias:

                The VP liked the query for Question #1, but doesn’t like the name being split up into multiple columns.  She wants you to recreate the query from Question #1, but concatenate the name together in one field called Employee Name that consists of the Courtesy Title, First Name, Last Name with the appropriate spacing.  Make sure that your query only has Job Title, Employee Name, and Birth Date in its results.

Help:  In order to concatenate the fields in Access, you use the ampersand (&).  Remember that this is a VP you are giving the results to and you need to put spaces between each field name.  For instance, if you were to concatenate the Title of Courtesy with the Last Name, you would enter [TitleOfCourtesy]&" "&[LastName] into the field area after the alias.

Look up concatenate in the dictionary if you don’t know what it means.

Question 2
Job title	Employee Name	Date of birth
Vice President	Mrs. Julie Jones	1/9/1954
Physician	Miss Rose Radford	12/12/1956
Physician	Mr. John Butcher	8/9/1964
Midwife	Mrs. Paula Tree	7/1/1970
Midwife	Ms. Ingrid Ikemire	9/1/1971

 

3. Single table with text criteria and hiding columns:

                The VP says it’s starting to look good, but now wants to send a special gift to employees that have a Job Title of “Midwife” since that group won a quality award.  However, she doesn’t want the Job Title shown as part of the final results so your query should only show Employee Name and Birth Date.

Question 3
Employee Name	Date of birth
Mrs. Paula Tree	7/1/1970
Ms. Ingrid Ikemire	9/1/1971

 

4. Single table using the asterisk for column selection and wildcard criteria:

                The VP says that she took a phone call yesterday from one of our patients.  The call was a “Liz” somebody.  She now wants a query that shows all the fields for a patient (use the asterisk) and wants anybody that has the characters “liz” in their first name and specifically requested that first name not appear twice in the query.