Print a report with three tables

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

Guest

I need to print a report for an employee (table1) that has taken different
seminars (table2) the names of the seminars are in another table (table2.1)
and the employees also has taken different exams (table3 and table3.1).
table1 and table2 are related with an IdEmployee field, but table3 is related
through another table (table4). I want to print all seminars and all exams
each employee has taken.

Also some employees haven't seminars or exams at all.
 
Theennus said:
I need to print a report for an employee (table1) that has taken different
seminars (table2) the names of the seminars are in another table (table2.1)
and the employees also has taken different exams (table3 and table3.1).
table1 and table2 are related with an IdEmployee field, but table3 is related
through another table (table4). I want to print all seminars and all exams
each employee has taken.

Also some employees haven't seminars or exams at all.
Click to expand...


The key is to create a query that associates the data
properly. In this case it's just a matter of add all 3
(or 5?) tables to the query. Then drag the linking fields
from one table to its related field in another table. Once
the connecting lines are in place, double click on each line
and select the appropriate join type.

If you switch the query to SQL view, it will probably look a
little like this outline:

SELECT tbl1.Employee, [tbl3.1].seminarname
FROM (tbl1 RIGHT JOIN tbl2 ON tbl1.empID = tbl2.empID)
LEFT JOIN [tbl2.1] ON tbl2.deminarID = [tbl2.1].seminarID

With the proper query, the report is relatively simple to
create.
 
Marshall Barton said:
Theennus said:
I need to print a report for an employee (table1) that has taken different
seminars (table2) the names of the seminars are in another table (table2.1)
and the employees also has taken different exams (table3 and table3.1).
table1 and table2 are related with an IdEmployee field, but table3 is related
through another table (table4). I want to print all seminars and all exams
each employee has taken.

Also some employees haven't seminars or exams at all.
Click to expand...


The key is to create a query that associates the data
properly. In this case it's just a matter of add all 3
(or 5?) tables to the query. Then drag the linking fields
from one table to its related field in another table. Once
the connecting lines are in place, double click on each line
and select the appropriate join type.

If you switch the query to SQL view, it will probably look a
little like this outline:

SELECT tbl1.Employee, [tbl3.1].seminarname
FROM (tbl1 RIGHT JOIN tbl2 ON tbl1.empID = tbl2.empID)
LEFT JOIN [tbl2.1] ON tbl2.deminarID = [tbl2.1].seminarID

With the proper query, the report is relatively simple to
create.
Click to expand...

Thanks, I tried it but I think a oversimplified my problem. Because some of
the employees haven't take a seminar, they don't appear, also those who
haven't take an exam don't appear. The query show me all those employees who
has taken a seminar and an exam. I think what I want to do is to have
something like 2 or more Details-section on my report for a single Group
Header, and also to show on the report those employees who haven't take a
seminar nor an exam.
 
Theennus said:
Marshall Barton said:
The key is to create a query that associates the data
properly. In this case it's just a matter of add all 3
(or 5?) tables to the query. Then drag the linking fields
from one table to its related field in another table. Once
the connecting lines are in place, double click on each line
and select the appropriate join type.

If you switch the query to SQL view, it will probably look a
little like this outline:

SELECT tbl1.Employee, [tbl3.1].seminarname
FROM (tbl1 RIGHT JOIN tbl2 ON tbl1.empID = tbl2.empID)
LEFT JOIN [tbl2.1] ON tbl2.deminarID = [tbl2.1].seminarID

With the proper query, the report is relatively simple to
create.
Click to expand...
Click to expand...
Theennus said:
Thanks, I tried it but I think a oversimplified my problem. Because some of
the employees haven't take a seminar, they don't appear, also those who
haven't take an exam don't appear. The query show me all those employees who
has taken a seminar and an exam. I think what I want to do is to have
something like 2 or more Details-section on my report for a single Group
Header, and also to show on the report those employees who haven't take a
seminar nor an exam.
Click to expand...


It sounds like you selected the wrong Join Type. You want
the one that seleects all records from the employee table
and any related records from the other tables.

I'm not at all sure what/how you want to do with the exam
data in the report. But, if the all table in one query
doesn't integrate the exams the way you want, try having a
main report with just the employees and one subreport for
the seminars and another for the exams. At least, the
subreport approach just uses one table for each report's
record source (be sure to specify the linking fields
correctly in the subreport contro's Link Maset/Child
properties.
 
Back
Top