Ambiguous outer joins

[Karl]

Hello,

I am having a problem with a query. I am trying to make an equipment list
and I have 5 tables (Category, Sub Category, Component, Subcomponent and
Maintenance) linked linear except for the last two which are linked to the
same table. My goal is to make an equipment list that shows all the equipment
and maintenance required for each piece of equipment.

The table relationships look like this –
Category Table is linked to Subcategory table by category id

Sub Category table is linked to Component Table by subcategory id

Component Table is linked to subcomponent table by subcomponent id and to
the maintenance table by component id

Sub competent table is linked to maintenance table by maintenance Id. So
both the Component table and the Sub component table are linked separately to
the Maintenance table. This is so I can have one maintenance table that keeps
track of all maintenance of components and sub components.

When I build a equipment list query without adding the maintenance table the
query works fine because all the relationships are linear. However when I add
the maintenance table to the equipment list query it won’t work and I get an
error message that says “The SQL statement could not be executed because it
contains ambiguous outer joins. To force one of the joins to be performed
first create a separate query that performs the first join and then include
that query in your SQL statementâ€.

My Query SQL looks like this:

SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Name of Equipment], [Sub Component Table].[Component
Name], [Maintenence Info].[Maintenance Req'd]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID = [Components
table].ProductID) LEFT JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER JOIN
[Maintenence Info] ON ([Sub Component Table].[Maintenance ID] = [Maintenence
Info].[Maintenance ID]) AND ([Components table].[Equipment ID] = [Maintenence
Info].[Equipment ID]);

I would appreciate any help anyone could provide and creating this query so
I can query out maintenance required for my different pieces of equipment.

Thanks,
Karl

 

[KARL DEWEY]

Any tables/queries to the right of a left join has to also be a left join.

 

[Duane Hookom]

Karl,
There is at least one other reply in the tablesdbdesign news group where you
asked the same question. You now have at least two threads going and are
possibly wasting the time of many people who read and respond to questions.
Please confine your questions to a single news group.

 

[Karl]

Duane,
Thanks for NOT helping on either reply! I've posted on multiple groups as
I've grown tired of posting on one group and not ever getting any help on my
questions. At least with multiple posts I usually receive at least one
helpful comment.

Karl,
Thanks for attempting to solve my problem. I'm not too familar with Access
but if I understand you response correctly you are saying I can't join the
maintenence table to both the sub component and component table? My reason
for joining them both to the maintenence table is so I only have one table
that shows all my maintenence activities required for my equipment list and
then I can query this to see what maintenece has to be done on any equipment.
For example my category may be Boats, sub category 14 ft lund Boat -
component would be motor which requires a oil change (maintenence). I would
also have a sub component of the motor which would be a propeller which would
need to be replaced (maintenance). So there for I want a query that tells me
the motor needs an oil change and the propeller needs to be replaced. Is
this possible without having a seperate maintenence table for the component
(motor) and sub-components (propeller)?

Thanks,

Karl,
There is at least one other reply in the tablesdbdesign news group where you
asked the same question. You now have at least two threads going and are
possibly wasting the time of many people who read and respond to questions.
Please confine your questions to a single news group.

--
Duane Hookom
MS Access MVP

Hello,

I am having a problem with a query. I am trying to make an equipment list
and I have 5 tables (Category, Sub Category, Component, Subcomponent and
Maintenance) linked linear except for the last two which are linked to the
same table. My goal is to make an equipment list that shows all the
equipment
and maintenance required for each piece of equipment.

The table relationships look like this -
Category Table is linked to Subcategory table by category id

Sub Category table is linked to Component Table by subcategory id

Component Table is linked to subcomponent table by subcomponent id and to
the maintenance table by component id

Sub competent table is linked to maintenance table by maintenance Id. So
both the Component table and the Sub component table are linked separately
to
the Maintenance table. This is so I can have one maintenance table that
keeps
track of all maintenance of components and sub components.

When I build a equipment list query without adding the maintenance table
the
query works fine because all the relationships are linear. However when I
add
the maintenance table to the equipment list query it won't work and I get
an
error message that says "The SQL statement could not be executed because
it
contains ambiguous outer joins. To force one of the joins to be performed
first create a separate query that performs the first join and then
include
that query in your SQL statement".

My Query SQL looks like this:

SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Name of Equipment], [Sub Component Table].[Component
Name], [Maintenence Info].[Maintenance Req'd]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID = [Components
table].ProductID) LEFT JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER JOIN
[Maintenence Info] ON ([Sub Component Table].[Maintenance ID] =
[Maintenence
Info].[Maintenance ID]) AND ([Components table].[Equipment ID] =
[Maintenence
Info].[Equipment ID]);

I would appreciate any help anyone could provide and creating this query
so
I can query out maintenance required for my different pieces of equipment.

Thanks,
Karl

 

[KARL DEWEY]

No I did not say you can't join the maintenence table to both the sub
component and component table. Re-read my post.

Duane,
Thanks for NOT helping on either reply! I've posted on multiple groups as
I've grown tired of posting on one group and not ever getting any help on my
questions. At least with multiple posts I usually receive at least one
helpful comment.

Karl,
Thanks for attempting to solve my problem. I'm not too familar with Access
but if I understand you response correctly you are saying I can't join the
maintenence table to both the sub component and component table? My reason
for joining them both to the maintenence table is so I only have one table
that shows all my maintenence activities required for my equipment list and
then I can query this to see what maintenece has to be done on any equipment.
For example my category may be Boats, sub category 14 ft lund Boat -
component would be motor which requires a oil change (maintenence). I would
also have a sub component of the motor which would be a propeller which would
need to be replaced (maintenance). So there for I want a query that tells me
the motor needs an oil change and the propeller needs to be replaced. Is
this possible without having a seperate maintenence table for the component
(motor) and sub-components (propeller)?

Thanks,

Karl,
There is at least one other reply in the tablesdbdesign news group where you
asked the same question. You now have at least two threads going and are
possibly wasting the time of many people who read and respond to questions.
Please confine your questions to a single news group.

--
Duane Hookom
MS Access MVP

Hello,

I am having a problem with a query. I am trying to make an equipment list
and I have 5 tables (Category, Sub Category, Component, Subcomponent and
Maintenance) linked linear except for the last two which are linked to the
same table. My goal is to make an equipment list that shows all the
equipment
and maintenance required for each piece of equipment.

The table relationships look like this -
Category Table is linked to Subcategory table by category id

Sub Category table is linked to Component Table by subcategory id

Component Table is linked to subcomponent table by subcomponent id and to
the maintenance table by component id

Sub competent table is linked to maintenance table by maintenance Id. So
both the Component table and the Sub component table are linked separately
to
the Maintenance table. This is so I can have one maintenance table that
keeps
track of all maintenance of components and sub components.

When I build a equipment list query without adding the maintenance table
the
query works fine because all the relationships are linear. However when I
add
the maintenance table to the equipment list query it won't work and I get
an
error message that says "The SQL statement could not be executed because
it
contains ambiguous outer joins. To force one of the joins to be performed
first create a separate query that performs the first join and then
include
that query in your SQL statement".

My Query SQL looks like this:

SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Name of Equipment], [Sub Component Table].[Component
Name], [Maintenence Info].[Maintenance Req'd]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID = [Components
table].ProductID) LEFT JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER JOIN
[Maintenence Info] ON ([Sub Component Table].[Maintenance ID] =
[Maintenence
Info].[Maintenance ID]) AND ([Components table].[Equipment ID] =
[Maintenence
Info].[Equipment ID]);

I would appreciate any help anyone could provide and creating this query
so
I can query out maintenance required for my different pieces of equipment.

Thanks,
Karl

 

[Roger Carlson]

Good plan.

1) Piss-off one of the most knowledgeable and prolific posters on the
newsgroups.
2) Piss-off the rest by deliberately flouting one of the rules of
newsgroups: no multiple postings.

 

[Duane Hookom]

I did say "please" in my reply and only suggested the issue with
multi-posting. If you have not gotten replies in other threads, then you
might not be asking in the right group or you question might be unclear.

If your objective is to create a list, then your easiest alternative is to
use a report with subreports. If you don't think so then it would be
appropriate to reply with a reason why you can't or won't follow the suggest
rather than SHOUTING that my reply didn't help.

Both of your initial posts included:
"Sub competent table is linked to maintenance table by maintenance Id"
It would seem more "competent" if you told use if the maintenance Id was in
both tables and if it was unique in one or the other. It would be less
ambiguous to all readers if you would provide some sample data.

--
Duane Hookom
MS Access MVP

Duane,
Thanks for NOT helping on either reply! I've posted on multiple groups as
I've grown tired of posting on one group and not ever getting any help on
my
questions. At least with multiple posts I usually receive at least one
helpful comment.

Karl,
Thanks for attempting to solve my problem. I'm not too familar with Access
but if I understand you response correctly you are saying I can't join the
maintenence table to both the sub component and component table? My reason
for joining them both to the maintenence table is so I only have one table
that shows all my maintenence activities required for my equipment list
and
then I can query this to see what maintenece has to be done on any
equipment.
For example my category may be Boats, sub category 14 ft lund Boat -
component would be motor which requires a oil change (maintenence). I
would
also have a sub component of the motor which would be a propeller which
would
need to be replaced (maintenance). So there for I want a query that tells
me
the motor needs an oil change and the propeller needs to be replaced. Is
this possible without having a seperate maintenence table for the
component
(motor) and sub-components (propeller)?

Thanks,

Karl,
There is at least one other reply in the tablesdbdesign news group where
you
asked the same question. You now have at least two threads going and are
possibly wasting the time of many people who read and respond to
questions.
Please confine your questions to a single news group.

--
Duane Hookom
MS Access MVP

Hello,

I am having a problem with a query. I am trying to make an equipment
list
and I have 5 tables (Category, Sub Category, Component, Subcomponent
and
Maintenance) linked linear except for the last two which are linked to
the
same table. My goal is to make an equipment list that shows all the
equipment
and maintenance required for each piece of equipment.

The table relationships look like this -
Category Table is linked to Subcategory table by category id

Sub Category table is linked to Component Table by subcategory id

Component Table is linked to subcomponent table by subcomponent id and
to
the maintenance table by component id

Sub competent table is linked to maintenance table by maintenance Id.
So
both the Component table and the Sub component table are linked
separately
to
the Maintenance table. This is so I can have one maintenance table that
keeps
track of all maintenance of components and sub components.

When I build a equipment list query without adding the maintenance
table
the
query works fine because all the relationships are linear. However when
I
add
the maintenance table to the equipment list query it won't work and I
get
an
error message that says "The SQL statement could not be executed
because
it
contains ambiguous outer joins. To force one of the joins to be
performed
first create a separate query that performs the first join and then
include
that query in your SQL statement".

My Query SQL looks like this:

SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Name of Equipment], [Sub Component
Table].[Component
Name], [Maintenence Info].[Maintenance Req'd]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID =
[Components
table].ProductID) LEFT JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER
JOIN
[Maintenence Info] ON ([Sub Component Table].[Maintenance ID] =
[Maintenence
Info].[Maintenance ID]) AND ([Components table].[Equipment ID] =
[Maintenence
Info].[Equipment ID]);

I would appreciate any help anyone could provide and creating this
query
so
I can query out maintenance required for my different pieces of
equipment.

Thanks,
Karl