Left Join problem

verdes

Registered User.
Local time
Today, 10:16
Joined
Sep 10, 2012
Messages
49
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
 
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?
 
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
 
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.
 
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.
 
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.
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom