View Single Post
  #5  
Old June 7th, 2010, 03:19 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default Forcing an ambiguous outer join

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@uwe wrote in message
news:a909a46b37e7f@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



.