I am making a phone directory for my company. It is currently set up showing
information on every employee. For the salespeople, it also shows who their
secretary is. Now I want to make it so that for each secretary, it shows the
salespeople they support. Each secretary can support two or three
salespeople. I created a query showing Employee table, and added another
instance of the Employee table (named Employee_1 by Access), with the
relationship set up to show all records from Employee, and only records from
Employee_1 where SecretaryID=EmployeeID. Surely there is a way to dothis in
reverse, so I can show for each secretary, the salespeople's names she
supports. Any suggestions?
To show secretaries only and the salespeople supported by each
secretary an INNER JOIN will suffice, e.g.
SELECT E1.EmployeeID,
E1.Firstname AS SecrataryFirstname,
E1.LastName AS SecretaryLastName,
E1.PhoneNumber As SecretaryPhoneNumber,
E2.FirstName AS SalespersonFirstname,
E2.LastName AS SalespersonLastName,
E2.PhoneNumber As SalespersonPhoneNumber
FROM Employees AS E1 INNER JOIN Employees AS E2
ON E1.EmployeeID = E2.SecretaryID;
That would be fine as the source for a list of secretaries only, but
not as part of a general phone book. One way to do that would be to
use an OUTER JOIN:
SELECT E1.EmployeeID,
E1.Firstname,
E1.LastName,
E1.PhoneNumber,
E2.FirstName AS SalespersonFirstname,
E2.LastName AS SalespersonLastName,
E2.PhoneNumber As SalespersonPhoneNumber
FROM Employees AS E1 LEFT JOIN Employees AS E2
ON E1.EmployeeID = E2.SecretaryID;
These would both return multiple rows per secretary of course. In a
report you'd group by LastName then by FirstName and then by
EmployeeID, putting the names and phone number from E1 in the an
EmployeeID group header and those from E2 in the detail, making sure
the latter and its controls CanShrink. The reason for using the
EmpoyeeID as a header is that you could have two or more employees
with the same name (I once worked with two Maggie Taylors), so this
distinguishes them, but by having the last and first names as the
higher group levels the report is ordered by name.
Personally I'd adopt a different approach, which would work for both a
form or report, and include a subform or subreport in the form or
report to show the salespeople supported by each secretary. The main
form or report would be based on the Employees table (or in the case
of a form on a sorted query on the table); the subform or subreport
would be based on a query which returns only those employees supported
by secretaries:
SELECT *
FROM Employees
WHERE SecretaryID IS NOT NULL;
The subform or subreport would be linked to the main form or report by
having EmployeeID as its LinkMasterFields property and SecretaryID as
its LinkChildFields property. Set a subform's AllowAdditions and
AllowDeletions properties to False and for each control in it set the
Enabled property to False and the Locked property to True. Otherwise
it would be far too easy for users to inadvertently alter data or even
terminate an employee with extreme prejudice (well, maybe not quite
that, but they could possibly delete their record), thinking that they
are merely removing someone from a secretary's list of staff
supported. For that the SecretaryID column of the relevant employee
row should be made Null.
Ken Sheridan
Stafford, England
