Georgie said:
Heres the 2 tables involved:
EMPLOYEE
Emp_Num
Emp_Name
Date_of_Birth
Salary
Bonus
Manager_Num
SALES_REP
Rep_Code
Telephone
Mobile_Phone
Email
Region
The question, "for all representatives in the North Connaught region, list
their name, total salary (including bonus), mobile phone number, email
address and manager name."
How do I work this??
The Emp_Num of an employee's manager is in the manager number field, so I
have to query the same table again. I have to do it in one query. It's a
question on a college exam paper.
Georgie,
Note: I am also making the *huge* assumption that Emp_Num =
Rep_Code. The attributes have different names, and so must
represent different things, and yet, I can't see a way that the
tables can be JOINed without making this assumption.
Note: I am also making another assumption that Manger_Num = Emp_Num.
Please forgive the dates appended to the table names.
CREATE TABLE EMPLOYEE_20051129_1
(Emp_Num INTEGER
,Emp_Name TEXT(10)
,Date_of_Birth DATETIME
,Salary CURRENCY
,Bonus CURRENCY
,Manager_Num INTEGER
,CONSTRAINT pk_EMPLOYEE_20051129_1
PRIMARY KEY (Emp_Num)
)
Manager_Num cannot contain a Null and have a foreign key to Emp_Num
(Emp_Num is a primary key, and cannot have NULLs).
A CHECK constraint should be added to insure the integrity of
Manager_Num, but I've omitted that step.
CREATE TABLE SALES_REP_20051129_1
(Rep_Code INTEGER
,Telephone TEXT(15)
,Mobile_Phone TEXT(15)
,Email TEXT(255)
,Region TEXT(255)
,CONSTRAINT pk_SALES_REP_20051129_1
PRIMARY KEY (Rep_Code)
,CONSTRAINT fk_SALES_REP_20051129_1_EMPLOYEE_20051129_1_Emp_Num
FOREIGN KEY (Rep_Code)
REFERENCES EMPLOYEE_20051129_1 (Emp_Num)
)
Sample Data, EMPLOYEE_20051129_1:
1, BIG BOSS, 01/01/1900, 1000000, 10000000, Null
2, Jay, 02/02/1950, 100000, 20000, 1
3, Dee, 02/02/1950, 90000, 15000, 1
4, Jeff, 02/02/1950, 80000, 10000, 3
5, Mike, 02/02/1950, 70000, 5000, 3
6, George, 02/02/1950, 20000, 100, 5
Sample Data, SALES_REP_20051129_1:
3, 111-222-3333, 111-222-3334, (e-mail address removed), South Connaught
4, 111-333-4444, 111-333-4445, (e-mail address removed), North Connaught
5, 111-444-5555, 111-444-5556, (e-mail address removed), North Connaught
Query:
SELECT E1.Emp_Name
,E1.Salary + E1.Bonus
,S1.Mobile_Phone
,S1.Email
,(SELECT E01.Emp_Name
FROM EMPLOYEE_20051129_1 AS E01
WHERE E01.Emp_Num = E1.Manager_Num)
AS Manager
FROM EMPLOYEE_20051129_1 AS E1
INNER JOIN
SALES_REP_20051129_1 AS S1
ON E1.Emp_Num = S1.Rep_Code
WHERE S1.Region = "North Connaught"
Results:
Jeff, $90,000.00, 111-333-4445, (e-mail address removed), Dee
Mike, $75,000.00, 111-444-5556, (e-mail address removed), Dee
Based on the sample data I created above, the results are exactly
what I would expect.
Sincerely,
Chris O.
PS Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.
