LEFT/RIGHT/OUTER JOINS in a set of subqueries

[luckycharms]

Hi Folks,

I have the following query, and i'm getting errors about access not supporting the JOIN. I know I can't embed inner joins within outer joins. But what about right and left joins? When I make them all INNER JOINS or LEFT JOINS, access doesn't complain. If I make them all RIGHT JOINS, or mix them up (as I'd like), Access complains. Any ideas?

thanks in advance...

SELECT s.*, sn.SurvT, sn.CE, sn.SD, sn.Dec, sn.Tapr, sf.*
FROM ((Species s LEFT JOIN species_abundances sa ON s.ID = sa.SpeciesID)
RIGHT JOIN Lars_seedling_morph_nursery_data_Biotropica sn ON s.ID = sn.SpeciesID)
RIGHT JOIN Lars_Seedling_Morph_Field_Data_JEcology sf ON s.ID = sf.SpeciesID;

 

[GinaWhipp]

These might help...
http://allenbrowne.com/subquery-02.html

http://allenbrowne.com/subquery-01.html

 

[luckycharms]

Thanks GinaWhipp, but none of the errors in the pages above match my error ("JOIN expression not supported"). Any other ideas?

 

[luckycharms]

hmm, seems like Access only wants you to refer to fields one level of nesting down. The following works:

SELECT s.*, sn.SurvT, sn.CE, sn.SD, sn.Dec, sn.Tapr, sf.*
FROM ((Species s RIGHT JOIN species_abundances sa ON s.ID = sa.SpeciesID)
RIGHT JOIN Lars_seedling_morph_nursery_data_Biotropica sn ON sa.SpeciesID = sn.SpeciesID)
RIGHT JOIN Lars_Seedling_Morph_Field_Data_JEcology sf ON sn.SpeciesID = sf.SpeciesID;

 

[GinaWhipp]

I thought that information was on one of those pages but I guess not. Glad you got it working...