A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Forcing an ambiguous outer join



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 04:12 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default 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

Ads
  #2  
Old June 4th, 2010, 08:23 PM posted to microsoft.public.access.queries
Ken Snell
external usenet poster
 
Posts: 177
Default 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  
Old June 4th, 2010, 08:56 PM posted to microsoft.public.access.queries
AccessKay via AccessMonster.com
external usenet poster
 
Posts: 22
Default 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  
Old June 5th, 2010, 04:50 AM posted to microsoft.public.access.queries
Ken Snell
external usenet poster
 
Posts: 177
Default 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" [email protected] wrote in message
news:[email protected]
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  
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" [email protected] wrote in message
news:[email protected]
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  
Old June 7th, 2010, 04:54 PM posted to microsoft.public.access.queries
John Spencer[_2_]
external usenet poster
 
Posts: 4
Default 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" [email protected] wrote in message
news:[email protected]
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  
Old June 7th, 2010, 05:50 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default 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" [email protected] wrote in message
news:[email protected]
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  
Old April 15th, 2011, 01:43 AM
bioday bioday is offline
Banned
 
First recorded activity by OfficeFrustration: Apr 2011
Location: United States
Posts: 1
Send a message via ICQ to bioday
Default

Quote:
Well, i have a mac Windows 7 Home Premium Product Key and i'm looking to do the boot camp thing. i'm downloading windows 7 from the internet but i don't have a product key. my brother has a sony laptop with a product key on the bottom. will this key work for me? i know that usually if Office 2010 Activation Key you buy a program or something, it lets you use it for up to like 3 Office Enterprise 2007 Key computers or so.
www.officefrustration.com = OfficeFrustration - Reply to Topic
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 OfficeFrustration.
The comments are property of their posters.