Records won't show up

K

Karl

Hello,
I am trying to create a query that allows me to look up a maintenence
record of a component and also a maintenance record of a sub-component at the
same time.

I have 5 tables all connected linearlly except the maintenance table which
linked to both the component table (through component id) and sub component
table (through sub-component id). The links are as follows

Categories to Products to Components which is linked to both the sub
component and maintenance table. Then the sub-component table is also linked
to the same maintenance table.

It is set up this way as sometimes components need maintenance or the
sub-components of a particular component may need maintenance at some point
as well.

This works fine in my form where I enter the information. However when I
trying to query out all the maintenance required for my compoents and
subcomponents. It won't work. If I don't add the subompoents table to my
query it will query out the maintenance for components fine, but I want it to
show both a the same time so I can easily print out at list of all the
maintenence required for my components and sub-components. I tried to edit
join properties between component and sub component to show ALL records of
components but I get a message that says SQL could not be executed because it
contains ambiguous outer joins.


Here is the SQL of the query:
SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Component Name], [Sub Component Table].[Sub Component
Name], [Maintenence Info].[Maintenance Required]
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) INNER JOIN [Maintenence Info] ON [Components
table].[Equipment ID] = [Maintenence Info].[Equipment ID]) INNER JOIN [Sub
Component Table] ON ([Sub Component Table].[Sub component ID] = [Maintenence
Info].[Sub Component ID]) AND ([Components table].[Equipment ID] = [Sub
Component Table].[Equipment ID]);

I would appreciate any help I could get on this problem

Thanks in Advance,
Karl
 
J

Joshua A. Booker

Karl,

I'm glad you got your forms to work the way you want.

Try losing the join between the subcomponents table and the components table
in this query. Then on the join between maint and subcomp, choose 'include
all records from maint and only those from subcomp..etc'

HTH,
Josh
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ambiguous outer joins 6
Show all Criteria. 4
Joining tables for query 1
Bill of material type query 3
Append only new records 3
Iterative Query 1
Help with a query 1
Linked Tables Append Query(ies) 4

Top