Left Join problem

[verdes]

I am using Access 2007.
I have a table called auxiliary_tbl. It contains 3 foreign keys (chair, secretary, treasurer) to a table called individual_tbl.

I want to make a query on the auxiliary_tbl that includes the corresponding names of the 3 foreign keys in the individual_tbl.

Right now I have an error "join not supported". I have coded the following without success.

SELECT auxiliary_tbl.*,
a1.indvFirstname as chairfirstname, a1.indvLastname as chairlastname,
a2.indvFirstname as sectfirstname, a2.indvLastname as sectlastname,
a3.indvFirstname as treasfirstname, a3.indvLastname as treaslastname
FROM ((auxiliary_tbl)
left join individual_tbl a1 ON (a1.indvID = auxChair))
left join individual_tbl a2 ON a2.indvID = auxSecretary
left join individual_tbl a3 ON a3.indvID = auxTreasurerperson

I appreciate any help. If there's some other way to get the names, let me know.

Thanks

 

[jdraw]

It would be helpful to readers if you would tell us briefly in plain English WHAT you are trying to do. What do your tables represent?What relationships exist between/among tables?

 

[Pat Hartman]

Looks like the parentheses are not in the correct position. I would put the query in the QBE and let it add the parentheses. I think they should be:

FROM ((auxiliary_tbl
left join individual_tbl a1 ON a1.indvID = auxChair)
left join individual_tbl a2 ON a2.indvID = auxSecretary)
left join individual_tbl a3 ON a3.indvID = auxTreasurerperson

 

[verdes]

The auxiliary_tbl contains information about auxilary groups in our organization. Each group has officers: a chairperson, secretary, and treasurer.

I want to make a query that lists all the info in the auxiliary table along with the name of the chairperson, secretary, and treasurer.

The officer names are in the individual_tbl. The auxiliary_tbl has a foreign key to indvID for each officer.

I tried placing the parenthesis as suggested above. It didn't work either. I get a join syntax error.

 

[Pat Hartman]

Did you build this query in the QBE? If not, why not? The QBE is a big help in resolving syntax problems with joins since you just draw lines.

 

[verdes]

I can't build it in there. It doesn't do the joins right at all. It builds the SQL with "and". That's why I posted in the forum. I'll keep looking.

 

[Royce]

If it's adding " AND " in the joins sounds like your trying to link the same table three times. Instead add the individual_tbl three times (Access will name it individual_tbl1, individual_tbl2, etc.. You can rename them to a1, a2, a3) Then set up the joins you need for each of the three tables.

 

[jdraw]

I agree with Royce. If you can have people from the Individual table be chairperson, secretary, and treasurer, then you should have 3 instances of the individual table.

Good luck. Let us know how you're doing.

 

[verdes]

Yes. That works. Thank you so much.