reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a number of reports (not linked to any tables or queries) that
displays terms of reference for different jobs. I would like to add a field
to these reports that adds the name of the persons to which the terms relate.
i have a query which divides these people up into there respective job
titles, and i can attach a field that adds the name to the respective terms,
however this only works if there is only one person related to that terms.
there is one set of terms where there are a number of people with the same
job title. i would like a fresh set of terms for each person on the list.
all it does at the moment is adds a list of people onto one set of terms.
how do i do this (hope i havent confused things too much)
 
It looks to me like this is really a question of getting the logical model
right so that it accurately models the reality of the situation.

The entity types involved seem to be Employees, JobTitles, TermsOfReference,
so you'd start with these three tables.

The relationship type between Employees and JobTitles could be many-to-many,
if you are recording each employee's employment history in the organisation,
or it could be one-to-many if you are simply recording each employee's
current job title. For the former you'd need to introduce an
EmploymentHistory table to model the relationship type, with columns
Emp[loyeeID and JobTitle referencing the primary keys of the Employees and
JobTitles tables. For the latter you'd simply need a foreign key JobTitle
column in the Employees table.

Similarly the relationship type between TermsOfReference and JobTitles could
be many-to-many, if any one term of reference applies to more than one job
title, or it could be one-to-many if each term applies specifically to one
job title. So, again you might need a further table to model the
relationship type, or you might just need a foreign key JobTitle column in
TermsOfReference.

Whatever the appropriate model is in your case I'd see a report being based
on a query which joins the JobTitles and Employees tables, either directly or
via the EmploymentHistory table modelling the relationship between them. The
report would be grouped on the job title, which would be shown in a group
header, with the employees' names in the detail section.

In the event of your having an EmploymentHistory table the query would
restrict its result set to each employee's current job, assuming that's what
you want, i.e. with the latest EmploymentHistory.HireDate, e.g.

SELECT JobTitles.JobTitle,
FirstName & " " & LastName AS FullName
FROM Employees, EmploymentHistory AS EH1, JobTitles
WHERE Employees1.EmployeeID = EH1.EmployeeID
AND EH1.JobTitle = JobTitles.JobTitle
AND HireDate =
(SELECT MAX(HireDate)
FROM EmploymentHistory AS EH2
WHERE EH2.EmployeeID = EH1.EmployeeID);

Strictly speaking the above query does not need the JobTitles table as
JobTitle is in the EmploymentHistory table as a foreign key. I've included
it, however, against the possibility of your wanting the query to return
other columns from the JobTitles table.

The subreport would go in main report's detail section and be linked on the
JobTitle column. You should then get a list with each job title as a group
heading followed by each employee with that job title, with each employee's
name followed by the terms of reference applicable to their current job.

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top