SPRING 2008 KReeves CSE200 QUIZ#4

Name _________________________________________ Seat# __________

Lab Day/Time _______ Thursday 3:30pm _______ Friday 1:30pm

Instructions:

· Put away all books, papers, and calculators. Turn off all beepers and cell phones.

· Answers must be legible or they will be marked incorrect.

· Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query. Remember to put quotes on the like and text values and pound signs around dates.

· Be sure to put calculated expression field names in the Field location with no table name, then repeat the field name along with the formula below the query design view where designated.

· Aggregate functions: Group By, Sum, Avg, Min, Max, Count, Where, and Expression.

· REMEMBER that all the data is not shown in the tables; be sure your queries will work with additional/deleted records.

The following Access Tables represents staff utilization of a mid-size ad agency. Each record in the Job1 and Job2 tables represents a task performed on that job by a specific employee. Each employee is uniquely identified by an employee number. Answer the questions in the below problems based on the tables given below. Remember that your answers should still work if data is added or deleted from the tables.

1. (8 pts) Draw the relationship diagram between these 3 tables the same as you would see in Access (as explained in class). Specify each primary and foreign key for each table and draw the lines between them where applicable.

2. (2 pts) Why can’t Emp# be the primary key on the Job1 table?

Because the same employee can work on multiple jobs more so their emp# can appear multiple times on the table. It won’t uniquely identify each row on the table.

3. (6 pts) Design a query to list the taskid# and employee id for each employee who worked on a Job2 task which lasted at least 15 minutes on either July 1, 2006 or July 10, 2006.

Table ________Job2__________________________

Field	taskid#	Emp#	Minutes	Date
Table	Job2	Job2	Job2	Job2
Total
Sort
Show	X	X
Criteria			>= 15	#7/1/2006#
OR			>= 15	#7/10/2006#
OR

Put expressions here, if necessary:

Alternate solution: One line criteria but has #7/1/2006# OR #7/10/2006# for the Date.

4. (6 pts) Design a query that lists the employee’s id and date and minutes spent for only those tasks Job 1 that were between 15 and 20 minutes long (inclusive)

Table ________Job1__________________________

Field	Emp#	Date	Minutes
Table	Job1	Job1	Job1
Total
Sort
Show	x	x	x
Criteria			Between 15 and 20
OR
OR

Put expressions here, if necessary:

Also >=15 and <=20

Also two different fields for Minutes with >=15 in one criteria line and on the same line <=20 in the other Minutes criteria line

5. (6 pts) Using the Access Query design view below, design a query to list the Employees whose names have the letters “john” in them somewhere i.e. as the first name of John or as part of the last name (for example, Johnson). List each employee’s name, Employee#, and Job Rate. Sort your list by Job Rate (most expensive first) and then alphabetically by name.

Table __________Employees________________________

Field	Job Rate	Name	Employee#
Table	Employees	Employees	Employees
Total
Sort	Descending	Ascending
Show	X	X	X
Criteria		Like “*john*”
OR
OR

Put expressions here, if necessary:

6. (10 pts) Design a query summarizing the Job1 tasks by emp#. The list should include Emp#, the number of tasks they worked and the total number of minutes that they worked. Below the query design view, fill in the resulting dynaset for this query using the given Job1 table data.

Tables ______ Job1____________________________

Field	Emp#	Taskid#	Minutes
Table	Job1	Job1	Job1
Total	Group by	Count	Sum
Sort
Show	x	x	x
Criteria
OR
OR

Put expressions here, if necessary:

The field Taskid# can be any field in the Job1 table; check for this both above and below

Query6
Emp#	CountOfTaskid#	SumOfMinutes
1	2	35
3	3	33
4	1	17
7	2	11
8	1	8

7. (6 pts) An overhead fee of $5 per task is assessed for each task regardless of duration. Using the result from the previous query (which you named query6), write a query in the design view below which will return the Emp#, and the total amount of overhead fees for each employee for Job1. If an employee has not worked on this job, their name should not appear on this list.

Tables ________Query6__________________________

Field	Emp#	Overhead Fees
Table	Query6
Total
Sort
Show	x	x
Criteria
OR
OR

Put expressions here, if necessary:

Overhead Fees: [Query6]![CountofTaskid#]*5

The Taskid# part of the formula field has to match the Count field in Query6;

Must have the colon in the formula; do not need the table name (i.e. query6) which is optional

8. (6 pts) Currently clients are billed the cost of the tasks completed (hourly rate times hours) plus the overhead fee assessment. An alternative method of billing overhead has been suggested by management as follows: Hourly overhead costs will be billed at 25% of the hourly wage plus $3 per hour. So an Ad Exec for example with an hourly wage of $125/hour would have an hourly overhead charge of $125*.25 +3. Write a query to calculate the hourly overhead charge for each of the different employees listed on the employees table. If the Job Rate field is blank the overhead charge should simply be $3.

Table _________Employees_________________________

Field	Employee#	Name	Total Hourly Rate
Table	Employees	Employees
Total
Sort
Show	x	x	x
Criteria
OR

Put expressions here, if necessary:

Total Hourly Rate: NZ([Employees]![Job Rate])*.25 + 3