Question

I need to use SQL in microsoft access to deal with those problems. Have no ideal on how to edit the code. Here is the requirement.

Database Design and Relationships
1. Create Primary Key(s) as appropriate for all tables. In TIMESHEETS, employees can only log time to a given WORKCODE once per day (e.g., if someone works on R&D for 2 hours in the morning and 2 hours in the afternoon, it's entered on the timesheet as 4 hours for R&D).
2. Create relationships between tables and enforce referential integrity.

Queries
Create the following queries using the names shown (i.e., including the leading number):
1-NUMBER OF EMPLOYEES BY DEPARTMENT: answers the question, "How many employees work for each department?" Include fields: NUMBEROFEMPLOYEES, DEPARTMENT.
2-TOTALPAY BY DEPARTMENT: answers the question, "How much total salaries are paid to each department?" Include fields: DEPARTMENT and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
3-TOTALPAY BY EMPLOYEE: answers the question, "What are the salaries paid to each employee?" Include fields: EMPLOYEEID, DEPARTMENT, WORKDATE, WORKCODE, WORKCODES. WORKNAME, HOURS, PAYBYHOUR, and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
4-TOTALPAY BY JOBTITLE: answers the question, "How much total salaries are paid to each job title (e.g., Business Analyst, Business Consultant)?" Include fields: JOBTITLE and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
5-TOTALPAY BY PAYBAND: answers the question, "How much total salaries are paid for each pay band?" Include fields: PAYBAND and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
6-TOTALSALARIES: answers the question, "What is the total amount salaries paid"?

Just need the code like :

SELECT PRODID, PRODNAME, PRICE

FROM PRODUCTS

WHERE PRICE > 200;

EMPLOYEES

employee id	last name	first name	job title	payband	zone	department	street	city	state	zip code

PAYBANDS

PAYBAND	ZONE	PAYBYHOUR	JOBTITLE

TIMESHEETS

EMPLOYEEID	WORKDATE	WORKCODE	HOUR

WORKCODES

WORKCODE	WORKNAME	DESCRIPTION

Answer 1

1. Creating appropriate primary keys: This has to be decided looking at the data but we can make certain calls looking at the column names.

How to create primary key is access file:

Open a Microsoft access files with respective tables on it already or else can be added by using the object browser.

Right click on the table name and select design view and wherein primary key can be selected using primary key option.

Table1: Employees

EmployeeId seems to be ideal primary key. But if that is indeed an identity key then a combination of first name, last name, street, zip code seems to be next ideal primary key. Let us assume that Employee Id is not identity hence a primary key.

Table2: Paybands

Payband is a primary key as there cannot be more than one payband

Table3: TimeSheets

There is an explicit statement mentioned in the requirement that an employee can work in one department only in a day. Hence the primary key goes on a combination of EmployeeId, WorkDate, Workcode

Table4: Workcodes

Workcode column seems to be an ideal primary key. If there can be multiple worknames in a single workcode, then WorkName seems to be next ideal choice.

2. At the same place we can find option to create foreign keys known to establish referential integrity.

Foreign keys are mainly on key columns. EmployeeId from Employee table is referenced everywhere else as a foreing key.

Workcode from workcodes table is referenced as foreing key in Timesheets table

Payband from Paybands table is referenced as foreing key in Employee table

Queries:

2.1:NUMBER OF EMPLOYEES BY DEPARTMENT: answers the question, "How many employees work for each department?" Include fields: NUMBEROFEMPLOYEES, DEPARTMENT.

SELECT Count(EmployeeId) as NUMBEROFEMPLOYEES, DEPARTMENT

FROM Employee

Group By DEPARTMENT;

2.2 TOTALPAY BY DEPARTMENT: answers the question, "How much total salaries are paid to each department?" Include fields: DEPARTMENT and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.

Select Department, sum(Hours*PaybyHour) as TOTALPAY

FROM Employee e inner join PAYBANDS p on e.Payband = p.Payband

inner join TIMESHEETS t on t.EmployeeId = e.EmployeeId

group by Department

2.3-TOTALPAY BY EMPLOYEE: answers the question, "What are the salaries paid to each employee?" Include fields: EMPLOYEEID, DEPARTMENT, WORKDATE, WORKCODE, WORKCODES. WORKNAME, HOURS, PAYBYHOUR, and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.

Select e.EMPLOYEEID,sum(Hours*PaybyHour) as TOTALPAY

FROM Employee e inner join PAYBANDS p on e.Payband = p.Payband

inner join TIMESHEETS t on t.EmployeeId = e.EmployeeId

inner join Workcodes w on w.Workcode = t.Workcode

group by e.EMPLOYEEID

2.4-TOTALPAY BY JOBTITLE: answers the question, "How much total salaries are paid to each job title (e.g., Business Analyst, Business Consultant)?" Include fields: JOBTITLE and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.

Select p.JOBTITLE,sum(Hours*PaybyHour) as TOTALPAY

FROM Employee e inner join PAYBANDS p on e.Payband = p.Payband

inner join TIMESHEETS t on t.EmployeeId = e.EmployeeId

inner join Workcodes w on w.Workcode = t.Workcode

group by p.JOBTITLE

2.5-TOTALPAY BY PAYBAND: answers the question, "How much total salaries are paid for each pay band?" Include fields: PAYBAND and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.

Select p.Payband,sum(Hours*PaybyHour) as TOTALPAY

FROM Employee e inner join PAYBANDS p on e.Payband = p.Payband

inner join TIMESHEETS t on t.EmployeeId = e.EmployeeId

inner join Workcodes w on w.Workcode = t.Workcode

group by p.Payband

2.6-TOTALSALARIES: answers the question, "What is the total amount salaries paid"?

Select sum(Hours*PaybyHour) as TOTALPAY

FROM Employee e inner join PAYBANDS p on e.Payband = p.Payband

inner join TIMESHEETS t on t.EmployeeId = e.EmployeeId

inner join Workcodes w on w.Workcode = t.Workcode

Hope this helped!