Left Outer Joins from Two Tables using MS Access

Question

I'm working on an existing Access database trying to implement some changes which will allow the estimation of cutting times for my employer (We're an industrial company who fabricate Flight Cases). This should function on only certain types of components; We've got a Stock table, which holds information on each Stock item including it's category. A Specification table which is used in order to build an estimate/quotation, and a table named [Spec Components] which holds the list of Stock Items which are attached to a Specification.

The tables can be joined as follows : Stock INNER JOIN [Spec Components] ON Stock.ID = [Spec Components].[Stock ID]

Specification INNER JOIN [Spec Components] ON Specification.SpecID = [Spec Components].[Spec ID]

My problem is that I only want to apply cutting times to an item in [Spec Components] if the item is listed as "Panels", "Extrusions", "Hybrids" etc (which is information that can be queried via Stock.Category) and different variables are used depending on the type of item we're quoting for, for example a Fabricated Lid Case's Panel may require 18 cuts but a different case may require 26. The case type is something which can be retrieved via Specification.CaseType, and determines which type of case we're quoting for.

Initially I tried to tackle this problem using the fast and dirty solution of nested conditional statements within the SQL Query however eventually got the error "Query too complex", as there is a limit on the number of nested ifs.

What I'm attempting now is to use a separate table which contains the list of the different cuts/setups etc

Category | CaseType | Setups | Cuts | PCID
--------------------------------------------
Panels    | Lidmaker |  2     |  32  |    1
Panels    | Fab Lid  |  4     |  16  |    1
Extrusion | Lidmaker |  1     |  24  |    1

I then need to be able to access the contents of this table where applicable, but still be able to retrieve the values from my other tables for which the contents of the table are not applicable (Which, to me, identifies the need for a Left Outer Join on this table).

I can do this using design view in MS Access: MS Access Design View

However when I run the query I get this message, but I don't really understand what it's telling me to do, or how on earth I should separate the queries, perhaps I'm being silly? Ambiguous Outer Joins

The query itself goes something like this:

SELECT [Spec Components].Qty, Specification.Height, Specification.Width, Specification.Depth, IIf(Cutting.Cuts>0 And Cutting.Setup>0,(Cutting.Cuts*Stock.CutTime)+(Cutting.Setup*Stock.SetupTime),0)
FROM ((Stock INNER JOIN [Spec Components] ON Stock.ID = [Spec Components].[Stock ID]) INNER JOIN Specification ON [Spec Components].[Spec ID] = Specification.SpecID) LEFT JOIN Cutting ON (Stock.Category = Cutting.Category) AND (Specification.[Case Type] = Cutting.CaseType)
ORDER BY [Spec Components].[Stock ID];

You can't use LEFT OUTER JOINS from Cutting to Specification, if Specification eventually leads back to Stock using INNER JOINS. It makes your OUTER JOINS ambiguous. You may have to set up 2 separate queries and then LEFT JOIN them together to get what you need. — Johnny Bones, Nov 17 '14 at 14:36

score 1 · Accepted Answer · edited May 23 '17 at 12:12

EDIT : possible duplicate of Ambiguous left joins in MS Access

About the error message, you can find more information following these links:

To solve the issue, "You must specify which method should be used by changing one of the joins or by separating the query into two queries." (quoted from the second link).

Below is a way of handling the issue using a subquery.

SELECT
  Specified_Stock.Qty,
  Specified_Stock.Height,
  Specified_Stock.Width,
  Specified_Stock.Depth,
  IIf(
    Cutting.Cuts > 0 And Cutting.Setup > 0,
    (Cutting.Cuts * Specified_Stock.CutTime) + (Cutting.Setup * Specified_Stock.SetupTime),
    0
  )
FROM (
  SELECT
    [Spec Components].[Stock ID],
    [Spec Components].Qty,
    Specification.Height,
    Specification.Width,
    Specification.Depth,
    Stock.CutTime,
    Stock.SetupTime,
    Stock.Category,
    Specification.[Case Type]
  FROM Stock
  INNER JOIN [Spec Components] ON Stock.ID = [Spec Components].[Stock ID]
  INNER JOIN Specification ON [Spec Components].[Spec ID] = Specification.SpecID
) as Specified_Stock
LEFT JOIN Cutting ON (Specified_Stock.Category = Cutting.Category) AND (Specified_Stock.[Case Type] = Cutting.CaseType)
ORDER BY Specified_Stock.[Stock ID];

Thank you! I can see what you've done, this solves the problem nicely. This query is being used as the data source for a datasheet view form in MS Access; I have noticed that now this has been implemented the user is unable to delete a record from `[Spec Components]` via the datasheet subform. I'm lead to believe that this is due to there being a subquery and/or that there is a calculated field (`TotalTime`) so perhaps I'll have to add a new control/create another query to allow the user to do that. Kind Regards. — Andrew Maston, Nov 18 '14 at 08:59
I do not use MS Access but I can only assume that your hypothesis is correct: the subquery adds a layer probably preventing direct access to the [Spec Components] for removal. — cooltea, Nov 18 '14 at 09:03
I don't use it either if I can help it! I've been tasked with implementing some fixes/changes on this Access system prior to beginning work on a new desktop application and it pains me greatly. Thanks again. — Andrew Maston, Nov 18 '14 at 09:19

Left Outer Joins from Two Tables using MS Access

1 Answers1