Query Joins

[Janet]

Hello,

I'm having a problem joining three tables in query.

The first table 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!

 

[Les]

It's telling you that you can probably do what you want
with 2 queries, one within the other. In other words,
join 2 tables in a query, then use that query as input to
your next query, instead of joining all 3 tables in 1
query.

Sounds like query 1 would join applicants to contractors.
Do you want to select all companies, or only those who
have hired/paid contractors? (Maybe they have all paid
contractors). This will determine if you need an outer
join or not.

Query 2 would use query 1 and workers table. This would
be an outer join - all contractors from query 1 and any
associated workers.

Hope this helps.



-----Original Message-----