Single/Multi-Column reports

[Guest]

I have a report that has job records that need to be across the whole page.
With each group of records that are across the whole page, I have related
employee names (from 1-4 per job) that I want to show up horizontally with
their related job. My problem is with getting the employee names to show up
horizontally. I've tried a 4-columned subreport but it is wanting to list all
the employees from all the jobs instead of by the job. I've searched the help
and can't seem to find a solution that works with a subreport or multi-column
report. Any ideas would be appreciated. Thanks!

 

[Guest]

I'm not clear whether by 'jobs' you mean job titles for employees' positions
in the organisation or projects on which an employee works. I'll assume the
latter first as my guess is that's what's meant.

A multi-column subreport is the right approach. In the subreport control
(that's the control in the main report which houses the subreport) set the
LinkMasterFields and LinkChildFields properties to the name of the fields
which relate the employee to the job e.g. JobID. This will limit each
instance of the subreport to the current job.

You don't say what your tables are , but assuming each employee might work
on more than one job there is a many-to-many relationship between jobs and
employees, so I'd expect a Jobs table, an Employees table and a JobEmployees
table which models the relationship between the first two. This would have
two columns, e.g. JobID and EmployeeID referencing the primary keys of Jobs
and Employees. The subreport would be based on a query joining Employees and
JobEmployees, e.g.

SELECT JobID, FirstName, LastName
FROM Employees INNER JOIN JobEmployees
ON Employees.EmployeeID = JobEmployees.EmployeeID;

If by 'job' you mean an employee's position in the organisation then you'll
most likely have just two tables Jobs (i.e. job titles) and Employees with a
foreign key JobID in the latter, unless you record an employee's employment
history in the organisation in which case the relationship is again
many-to-many, so you'd have three tables. Assuming two tables then the
subreport would be based on the Employees table in this case, and linked on
the JobID fields in the same way.

Ken Sheridan
Stafford, England