Querying from the same table

[Ted Allen]

Hi,

I believe that your problem stems from the fact that you
want to use your table to represent two different
recordsets being joined, but you haven't defined it that
way. I usually work in the design grid of Access rather
than SQL, so I'll start by describing how to do it
there.

In the design grid, I would usually insert the same table
twice, and Access would give the second instance an alias
(it would add _1 to the name). Then, I would define the
relationship between the two (in your case Dept Reports
To would link to Dept ID). From then on, I would refer
to the fields from one table or another using the
appropriate table name before the field names.

Listed below is the SQL text from a query using this type
of alias. This query joins the table tMaster with two
instances of the table tzlookupOffices.

SELECT tMaster.ID, tMaster.[Project Title],
tzlookupOffices.[Office Name] AS [Design Office],
tzlookupOffices_1.[Office Name] AS [CM Office]
FROM (tzlookupOffices RIGHT JOIN tMaster ON
tzlookupOffices.[Office ID] = tMaster.[Des Dist_ID]) LEFT
JOIN tzlookupOffices AS tzlookupOffices_1 ON tMaster.[CM
Dist_ID] = tzlookupOffices_1.[Office ID];

In your case, you would be joining your two instances of
the Dept table directly, but hopefully this will give you
some ideas.

Hope that helps.

-Ted

-----Original Message-----
I have a department table:

Dept_id (primary key, id number for each department)
Dept_name (descriptive name)
Dept_manager (foreign key to emp_id in employee table)
Dept_reportsto (foreign key to department table, is the

department that this department reports to), this field
can be empty since the top departments don't report to
anyone (for the purpose of this application, anyway)

I'm trying to do a query that lists each department, who

the manager is and which department they report to.

This is what I have:

SELECT tbl_Department.DEPT_ID, tbl_Department.DEPT_NAME,

tbl_Department.DEPT_MANAGER, (select
tbl_department.Dept_name from tbl_Department where
tbl_department.dept_id = DEPT_REPORTSTO) AS ReportsTo

 

[Guest]

Thanks, Ted. That worked.

One last thing. I want all the records to display, even if they are null. I.E. For the purposes of this application, the VP's do not report to anyone, so that is a null value. How can I get those to display as well?

Em

 

[Ted Allen]

Hi Em,

Try changing the join between the two tables from an
inner join (the default) to a left or right join
(depending which table is listed first). This will say
that you want all of the records in your department
table, and the records from the reports to department
when there is a match. The default inner join will only
return records where there is a match.

To do this in the design grid, double-click or right
click on the join line between the tables and change the
join properties.

To do it in SQL, change the INNER JOIN to be RIGHT or
LEFT join. The right or left should refer to the side of
the table that you want to "drive" the query (the one
that you want to return all records from). So, if your
first instance of the department table is listed first,
then the second instance that will return the reports to
matches, you would use a left join.

Hope this helps. If not, post back.

-Ted Allen

-----Original Message-----
Thanks, Ted. That worked.

One last thing. I want all the records to display, even

if they are null. I.E. For the purposes of this
application, the VP's do not report to anyone, so that is
a null value. How can I get those to display as well?