If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Forcing an ambiguous outer join
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 needed to create a separate query and I think it was telling me to combine the two. So I tried to do so but I received a syntax error about a missing operator. I searched the previous posts and then added the parentheses. Would you mind helping me put these two SQLs together? Thanks!!! Qry1 SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], tblTransData.[project alias], tblTransData.[cost category], tblCostCat.TypeID, tblTransData.Trans_Date, tblTransData.TotHrs FROM tblCostCat LEFT JOIN tblTransData ON tblCostCat.CostCatNm = tblTransData.[cost category]; Qry2 SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], tblTransData.[project alias], tblTransData.[cost category], tblTransData.Trans_Date, tblTransData.TotHrs, tblCostCode.IndirectLabel FROM tblCostCode LEFT JOIN tblTransData ON tblCostCode.CostCode=tblTransData.[cost code]; My attempt to join them together: SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], tblTransData.[project alias], tblTransData.[cost category], tblCostCat.TypeID, tblTransData.Trans_Date, tblTransData.TotHrs, tblCostCode.IndirectLabel FROM tblCostCat LEFT JOIN tblTransData ON (tblCostCat.CostCatNm = tblTransData.[cost category]) AND tblCostCode LEFT JOIN tblTransData ON (tblCostCode.CostCode=tblTransData.[cost code]); This is where I received the missing operator error |
#2
|
|||
|
|||
Forcing an ambiguous outer join
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. -- Ken Snell http://www.accessmvp.com/KDSnell/ "AccessKay" wrote in message ... 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 needed to create a separate query and I think it was telling me to combine the two. So I tried to do so but I received a syntax error about a missing operator. I searched the previous posts and then added the parentheses. Would you mind helping me put these two SQLs together? Thanks!!! Qry1 SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], tblTransData.[project alias], tblTransData.[cost category], tblCostCat.TypeID, tblTransData.Trans_Date, tblTransData.TotHrs FROM tblCostCat LEFT JOIN tblTransData ON tblCostCat.CostCatNm = tblTransData.[cost category]; Qry2 SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], tblTransData.[project alias], tblTransData.[cost category], tblTransData.Trans_Date, tblTransData.TotHrs, tblCostCode.IndirectLabel FROM tblCostCode LEFT JOIN tblTransData ON tblCostCode.CostCode=tblTransData.[cost code]; My attempt to join them together: SELECT tblTransData.number, tblTransData.Empl, tblTransData.[cost code], tblTransData.[project alias], tblTransData.[cost category], tblCostCat.TypeID, tblTransData.Trans_Date, tblTransData.TotHrs, tblCostCode.IndirectLabel FROM tblCostCat LEFT JOIN tblTransData ON (tblCostCat.CostCatNm = tblTransData.[cost category]) AND tblCostCode LEFT JOIN tblTransData ON (tblCostCode.CostCode=tblTransData.[cost code]); This is where I received the missing operator error |
#3
|
|||
|
|||
Forcing an ambiguous outer join
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 |
#4
|
|||
|
|||
Forcing an ambiguous outer join
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 |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
Forcing an ambiguous outer join
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@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 . |
#7
|
|||
|
|||
Forcing an ambiguous outer join
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@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 . . |
#8
|
|||
|
|||
Quote:
|
Thread Tools | |
Display Modes | |
|
|