Query Joins

[Janet]

Hi there,

I'm having a problem with joins in a query. It's a
little complicated to describe, but I have a database in
which there is a table with employers, a table with
contractors who work for the employers, and then a third
table with other employees who work for either the
employers or the contractors(but generally the
contractors are the employers of these people). I want
to create a query/report which is sorted by the main
employers and shows all the contractors who work for the
employers, but also all the employees that work for each
contractor. I tried to do a join within the query to
show all the contractors, and only the employees that
work for those contractors, but I get an error message
saying my joins are questionable. I know this sounds
complicated, but does anyone know how to solve this
problem? Thanks!

Janet

 

[Jeff Boyce]

Janet

What are the common fields from table1 (tblEmployer) to table2
(tblContractor), and again, from table2 (tblContractor) to table3
(tblContractorEmployee)?

What is the error message? -- I'm not familiar with "joins are
questionable".

An observation -- how many of these are persons, and how many are
organizations/companies? You've capture "role" information in your table
structure (not always a good idea!) As you've pointed out, an "employer"
might be an Employer or a Contractor.

 

[Janet]

Jeff,

I'll try to explain a bit better: I have three tables.
The first one is called applicants. Applicants are big
companies who hire either contractors or individual
employees. The second table is called contractors. They
work for the applicants, and they may or may not have
workers working for them. The third table is workers,
who either work for the applicants directly, or
indirectly through the contractors who hire them. The
purpose of the database is to show moneys which have been
paid out by a trust to the contractors and sometimes
through the contractors to the workers.

I am trying to create a report that is sorted by the
applicant (company) and lists all the contractors that
work for them and the moneys paid out to them. I also
want to show any moneys paid out to workers (but this
would be a sum total) that are attached to contractors.
So the list would show all contractors, including those
who do not have workers attached to them. I can create a
query which shows all the contractors who have workers
attached to them, but not a list that also includes
contractors who do not. I realize I have to create an
outer join to make this happen, but when I try to, I get
the following error message:

The SQL statement could not be executed because it
contains ambiguous outer joins. To force one of the
joins to be performed first, create a separate query that
performs the first join and then include that query in
your SQL statement.

I don't know what that means. My common fields in the
tables are Contractor Number and Applicant Number.

Thanks for your help!

Janet

 

[fofa]

TB1 (Employers)
EKey
Col1
Col2

TB2 (Contractors)
CKey
EKey
Col1
Col2

TB3 (MoreEmployees)
MEKey
CKey
Col1
Col2

This does not appear to be a good setup, but if I am anywhere correc
in the table design, AND your question, try this:
SELECT TB1.Col1, TB2.Col1, TB3.Col1
FROM TB1
INNER JOIN TB2 ON TB2.EKay = TB1.EKey
LEFT JOIN TB3 ON TB3.CKey = TB2.CKey
Order by TB1.Col

 

[Jeff Boyce]

Janet

The error message is suggesting that you break your query into more than
one. Do one of the joins in the first query, then create a second query,
using the first as one of your sources.