Forcing an ambiguous outer join
in [Queries]
|
Prev: JOIN Query very slow
Next: DatePart
From: John Spencer on 7 Jun 2010 11:54 As a guess you want a query that looks like SELECT tblTransData.[ID-PK] , tblTransData.Number , tblTransData.Empl , IndirectLabel , TypeID , Hours FROM (tblTransData LEFT JOIN tblCostCat ON tblTransData.[Cost Category] = tblCostCat.[CostCatNm-pk]) LEFT JOIN tblCostCode ON tblTransData.[Cost Code] = tblCostCode.[CostCode-PK] AccessKay wrote: > I'm finding this hard to put into words without giving you my table structure. > > tblTransData > ID-PK > Number > Empl > Cost Code (I want the Indirect Label from tblCostCode) > Cost Category (I want the TypeID from tblCostCat) > Hours > > tblCostCat > CostCatNm-PK > TypeID > > tblCostCode > CostCode-PK > Description > IndirectLabel > > What do I need to do to get this to work. How should I change my structure. > Thanks again for your help. > > > "Ken Snell" wrote: > >> OK your query structure essentially is this: >> >> tblCostCode ---> tblTransData <--- tblCostCat >> >> This structure is unusual because the table of greatest interest (as noted >> by your query's output fields) is on the right side of all the joins. But by >> using the join setup, this tblTransData table may have no records that match >> the other two tables' data keys. >> >> Tell us in words what you want your query to select in terms of data >> records. Let's get your query in the right shape. >> -- >> >> Ken Snell >> http://www.accessmvp.com/KDSnell/ >> >> >> >> "AccessKay via AccessMonster.com" <u59222(a)uwe> wrote in message >> news:a909a46b37e7f(a)uwe... >>> Thanks Ken for your reply. My original SQL is as follows: >>> >>> SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], >>> tblTransData.[project alias], tblTransData.[cost category], >>> tblCostCat.TypeID, >>> tblTransData.Trans_Date, tblTransData.TotHrs, tblCostCode.IndirectLabel >>> FROM tblCostCode LEFT JOIN (tblCostCat LEFT JOIN tblTransData ON >>> tblCostCat. >>> CostCatNm = tblTransData.[cost category]) ON tblCostCode.CostCode = >>> tblTransData.[cost code]; >>> >>> >>> Ken Snell wrote: >>>> How about if you post the original query's SQL where you got the error >>>> message about ambigous joins? Let's see if we can debug that query before >>>> we >>>> come to any conclusions about whether you need to combine these two >>>> queries >>>> or not. >>>> >>>>> I tried to add another table to my query with a Left join and I receive a >>>>> message that my SQL contains ambiguous joins. It went on to say that I >>>> [quoted text clipped - 28 lines] >>>>> This is where I received the missing operator error >>> -- >>> Message posted via http://www.accessmonster.com >>> >> >> . >>
From: AccessKay on 7 Jun 2010 12:50
You're good at guessing John! I put this in and it looked somewhat similar to what I was doing but then I noticed that you switched the directions of the joins (going FROM tblTransData TO tblCostCat and tblCostCode). If I just use tblCostCat, it works when I place the join FROM tblCostCat TO tblTransData. I also just tried it the other way to see if it works and it does. I find that odd that it would work both ways. I'll have to reprogram my brain to do it this way from now on. I really appreciate your help with this! "John Spencer" wrote: > As a guess you want a query that looks like > > SELECT tblTransData.[ID-PK] > , tblTransData.Number > , tblTransData.Empl > , IndirectLabel > , TypeID > , Hours > FROM (tblTransData LEFT JOIN tblCostCat > ON tblTransData.[Cost Category] = tblCostCat.[CostCatNm-pk]) > LEFT JOIN tblCostCode > ON tblTransData.[Cost Code] = tblCostCode.[CostCode-PK] > > AccessKay wrote: > > I'm finding this hard to put into words without giving you my table structure. > > > > tblTransData > > ID-PK > > Number > > Empl > > Cost Code (I want the Indirect Label from tblCostCode) > > Cost Category (I want the TypeID from tblCostCat) > > Hours > > > > tblCostCat > > CostCatNm-PK > > TypeID > > > > tblCostCode > > CostCode-PK > > Description > > IndirectLabel > > > > What do I need to do to get this to work. How should I change my structure. > > Thanks again for your help. > > > > > > "Ken Snell" wrote: > > > >> OK your query structure essentially is this: > >> > >> tblCostCode ---> tblTransData <--- tblCostCat > >> > >> This structure is unusual because the table of greatest interest (as noted > >> by your query's output fields) is on the right side of all the joins. But by > >> using the join setup, this tblTransData table may have no records that match > >> the other two tables' data keys. > >> > >> Tell us in words what you want your query to select in terms of data > >> records. Let's get your query in the right shape. > >> -- > >> > >> Ken Snell > >> http://www.accessmvp.com/KDSnell/ > >> > >> > >> > >> "AccessKay via AccessMonster.com" <u59222(a)uwe> wrote in message > >> news:a909a46b37e7f(a)uwe... > >>> Thanks Ken for your reply. My original SQL is as follows: > >>> > >>> SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], > >>> tblTransData.[project alias], tblTransData.[cost category], > >>> tblCostCat.TypeID, > >>> tblTransData.Trans_Date, tblTransData.TotHrs, tblCostCode.IndirectLabel > >>> FROM tblCostCode LEFT JOIN (tblCostCat LEFT JOIN tblTransData ON > >>> tblCostCat. > >>> CostCatNm = tblTransData.[cost category]) ON tblCostCode.CostCode = > >>> tblTransData.[cost code]; > >>> > >>> > >>> Ken Snell wrote: > >>>> How about if you post the original query's SQL where you got the error > >>>> message about ambigous joins? Let's see if we can debug that query before > >>>> we > >>>> come to any conclusions about whether you need to combine these two > >>>> queries > >>>> or not. > >>>> > >>>>> I tried to add another table to my query with a Left join and I receive a > >>>>> message that my SQL contains ambiguous joins. It went on to say that I > >>>> [quoted text clipped - 28 lines] > >>>>> This is where I received the missing operator error > >>> -- > >>> Message posted via http://www.accessmonster.com > >>> > >> > >> . > >> > . > |