Joining tables for query

[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

 

[[MVP] S.Clark]

Typically it means that all of your little link arrows don't point the same
direction. Be it all Left, all right, all to the child, all away from the
child, all to the parent, all away from the parent, or some other valid
option.

Keep working with the join properties until you get it right.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

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