Given the tables and associated data that you can copy and load into Access (see

Question

Given the tables and associated data that you can copy and load into Access (see below), generate the following reports using Report Wizard

1. Within project name (not proj_code), for each job description (not job code), show the total charges for each employee sorted by last_name/first_name. The main sort is project description and the secondary is last name. Subtotal total charges by project and then an overall, grand total. All dollar amounts are in Currency format

Partial Sample Output

2. For each employee (sorted ascending by last name), list all projects (sorted in project name ascending) that employee worked on including job description and total charges. Subtotal each employee’s total charges and then print a grand total for all employees. All dollar amounts are in Currency format.

Partial Sample Output – Note how last name, first name and job description all appear on the same line. You can sort by each but then move the fields up in the design view since there’s no need to repeat them or have them on separate lines.

3. For each project name, show the highest paid employee’s first name and last name, job description and total charges for that project

4. Show the PK, FK and relationships between the four tables.

5. Show the SQL code for the queries you developed to produce these reports.

TABLES AND DATA

EMPLOYEE

EMP_ID

EMP_LNAME

EMP_FNAME

EMP_INITIAL

EMP_AREA_CODE

EMP_PHONE

101

Newson

John

D

653

234-3245

105

Schwann

David

F

653

234-1123

108

Sattlemeier

June

H

905

554-7812

110

Ramoras

Anne

R

615

233-5568

EMP_ID

EMP_LNAME

EMP_FNAME

EMP_INITIAL

EMP_AREA_CODE

EMP_PHONE

101

Newson

John

D

653

234-3245

105

Schwann

David

F

653

234-1123

108

Sattlemeier

June

H

905

554-7812

110

Ramoras

Anne

R

615

233-5568

CHARGE

PROJ_CODE

JOB_CODE

EMP_ID

CHG_HOURS

1

CT

105

16.20

1

CT

110

14.30

1

EE

101

13.30

2

BE

108

17.50

2

EE

101

19.80

3

CT

105

23.40

3

CT

110

11.60

PROJ_CODE

JOB_CODE

EMP_ID

CHG_HOURS

1

CT

105

16.2

1

CT

110

14.3

1

EE

101

13.3

2

BE

108

17.5

2

EE

101

19.8

3

CT

105

23.4

3

CT

110

11.6

JOB

JOB_CODE

JOB_DESCRIPTION

JOB_CHARGE

BE

Biological Engineer

55.00

CT

Computer Technician

62.00

EE

Electrical Engineer

65.00

JOB_CODE

JOB_DESCRIPTION

JOB_CHARGE

BE

Biological Engineer

55

CT

Computer Technician

62

EE

Electrical Engineer

65

PROJECT

PROJ_CODE

PROJ_NAME

1

Hurricane

2

Coast

3

Satellite

PROJ_CODE

PROJ_NAME

1

Hurricane

2

Coast

3

Satellite

EMPLOYEE

EMP_ID

EMP_LNAME

EMP_FNAME

EMP_INITIAL

EMP_AREA_CODE

EMP_PHONE

101

Newson

John

D

653

234-3245

105

Schwann

David

F

653

234-1123

108

Sattlemeier

June

H

905

554-7812

110

Ramoras

Anne

R

615

233-5568

Costs by Job within Project Project Name Job Description Coast Biological Engineer SubTotal Biological Engineer Electrical Engineer SubTotal Electrical Engineer Summary Coast Hurricane Computer Technician SubTotal Computer Technician Electrical Engineer SubTotal Electrical Engineer Summary Hurricane Last name Sattlemeier Newson Ramoras Schwann Newson First name June John Anne David John Charges $962.50 $962.50 $1,287.00 $1,287.00 $2,249.50 $886.60 $1,004.40 $1,891.00 $864.50 $864.50 $2,755.50

Explanation / Answer

Answers:

4. Show the PK, FK and relationships between the four tables.

   Emp_ID --> Primary key in Employee table, Foreign Key in Charge table

PROJ_CODE --> Primary key in PROJECT table, Foreign Key in Charge table

JOB_CODE --> Primary key in JOB table, Foreign Key in Charge table

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.