Hi all-

On my current project, I've come across a situation with the database (MS Access) which has me stuck.

I have 2 tables. One is a list of film productions. It has (among others) a ProducerID column and a CoProducerID column.
The other table is named Directory, and lists names, addresses, etc. It has a DirectoryID column, and a DisplayName column.

Here's the problem: in the Productions table, BOTH the ProducerID column and the CoProducerID column are related to the DirectoryID column in the Directory table. In other words, some records in the Directory table are for Producers, and some are for Co-Producers.
I need to create a SQL SELECT query which will pull the DisplayName column for the Producers AND for the Co-Producers!

It's easy enough to do a LEFT OUTER JOIN to pull the DisplayName for the Producer, joining the ProducerID column with the DirectoryID. The problem is, how to get the DisplayName for the CoProducerID column??

I can't join the same 2 tables twice, right?

If anyone can help, I would greatly appreciate it...

Thanks,

-Andrew