Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Joining tables for query

Discussion in 'Information Technology' started by [MVP] S.Clark, Jul 28, 2006.

  1. [MVP] S.Clark

    [MVP] S.Clark
    Expand Collapse

    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

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

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

    "Karl" <Karl@discussions.microsoft.com> wrote in message
    > 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
  2. Loading...

    Similar Threads Forum Date
    India Indian group warns women joining 'SlutWalk' Breaking News Jun 28, 2011
    Group wants Sikhs to get with the times and prevent youth from joining gangs Hard Talk Sep 4, 2010
    India 11 Constables Get Pregnant at Training School Breaking News Apr 26, 2011
    Warm Up with Winter Vegetables (Vegetarian Casseroles) Cooking & Recipies Feb 7, 2011
    India Two constables arrested for beating, extortion Breaking News Jul 27, 2010

Share This Page