Ambiguous Outer Joins

[vwhite]

Hi all,

I have a mysterious problem. I have a query with just 2 Tables with a one to many relationship between them. I try o create a RIGHT JOIN between the related fields.

When I run it I get the Ambiguous Outer Joins error. I am wondering how a query with only one outer join can be ambiguous ?

If I try it with another related table to the same parent table I do not get the error, it just seems to be this one related table.

Any ideas.
Thanks in advance....

....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[Steve101]

might be related to the primary / foreign key / index definitions on the associated tables. Check these out.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Transcend]

Well it'd help if you pasted your query

Why are you using an outer join?

Transcend

 

[vwhite]

Well now this IS strange. After you asked me to paste query I went back and actually typed in the query and here it is

SELECT PB_Assessment_WeedSpecies_tbl.PBAssessmentCode, PB_Assessment_WeedSpecies_tbl.WeedName, PB_Assessment_WeedSpecies_tbl.WeedTypeId, lkp_WeedTypes_tbl.WeedTypeValue
FROM lkp_WeedTypes_tbl RIGHT JOIN PB_Assessment_WeedSpecies_tbl ON lkp_WeedTypes_tbl.WeedTypeId = PB_Assessment_WeedSpecies_tbl.WeedTypeId;


However this works.

But if I try and do the same thing in the Access Query builder I get the Ambiguous Outer Joins error.

It seems I now have a work around anyway.

I couldn't see anything wrong with the primary/foreign key/index definitions.

By the way I am using an outer join as I have a bound form that does not have the data filled in yet, but I also use the data in the lookup table in the underlying query to display on the form when they have selected a value

i.e. when they select a Weed Type, it then displays the Weed Type Value ....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama