Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Question Unanswered: SQL Triple Join Woes

    Hi guys,

    Suffering a little here, so could do with a hand (although I'm not sure how easy it will be to help...)

    I have an SQL statement that I use to populate a listbox, which looks like:

    Code:
    "SELECT main.[Product Code], ref_products.[Product vName] AS " & _
    "[Product Description], " & _
    "COUNT(main.[ID]) AS [Quantity] " & _
    "FROM (" & _
        "main LEFT JOIN ref_products ON " & _
        "ref_products.[Product Code] = main.[Product Code]) " & _
            "INNER JOIN prodSched_plan ON " & _
            "main.[Order Number] = prodSched_plan.[Order Number] " & _
    "WHERE main.[Order Number] = " & Me.oNum.Value & " " & _
    "AND main.[Production Date] IS NULL " & _
    "AND prodSched_plan.[Production Date] <> #" & Me.OpenArgs & "# " & _
    "GROUP BY main.[Product Code], ref_products.[Product vName]"
    Now, to explain it a little... I have a table main, that has the vast bulk of my data... The ref_products table contains information about each product.

    The Me.OpenArgs will contain the current [Production Date] being worked on, which will be the same as the main.[Production Date].

    The product part works fine, no problems there... The big problem that I can't solve lies with the prodSched_plan and the COUNT function.

    Effectively, what I want this to do is:

    - Look at the main.[Production Date] field, for the chosen [Order Number]. Remembering the number of records that don't have a [Production Date].
    (this is effectively the number of products still to produce for that order)

    - Look at the prodSched_plan.[Production Date] field, for the chosen [Order Number]. Remembering the number of records that do match the [Production Date].
    (this is effectively the number of products that have been entered onto the production
    plan for that order)

    - Subtract the second number found (from the prodSched_plan) from the first number found (from the main table).
    (this will effectively give me the number of products that have yet to be produced, and
    also don't already appear on the production plan)


    If anyone could shed some light as to how to perform these maths functions in SQL, and pull the correct numbers from it in the first place, I would be most grateful.

    Thanks a lot guys, hopefully my explanation is sufficient. Any queries give me a shout.
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by kez1304 View Post
    - Look at the prodSched_plan.[Production Date] field, for the chosen [Order Number]. Remembering the number of records that do match the [Production Date].
    Just to be sure: do you mean [Production Date] supplied in Me.Openargs?
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Realised I needed subselects instead of a triple join (unless someone wants to correct me..?).

    As far as I can tell the following works how I want:

    Code:
        strSQL = "SELECT main.[Product Code], ref_products.[Product vName] AS " & _
                 "[Product Description], " & _
                 "(" & _
                   "(" & _
                     "SELECT COUNT(1) AS [Quantity 2] " & _
                     "FROM main " & _
                     "WHERE main.[Order Number] = " & Me.oNum & " " & _
                     "AND main.[Production Date] IS NULL " & _
                   ")" & _
                 " - " & _
                   "(" & _
                     "SELECT IIF(ISNULL(SUM(prodSched_plan.[Quantity])), " & _
                       "0, SUM(prodSched_plan.[Quantity])) " & _
                     "FROM prodSched_plan " & _
                     "WHERE prodSched_plan.[Order Number] = " & Me.oNum & _
                   ")" & _
                 ") AS [Quantity] " & _
                 "FROM main LEFT JOIN ref_products ON " & _
                     "ref_products.[Product Code] = main.[Product Code] " & _
                 "WHERE main.[Order Number] = " & Me.oNum.Value & " " & _
                 "AND main.[Production Date] IS NULL " & _
                 "GROUP BY main.[Product Code], ref_products.[Product vName] "
    Looking for the perfect beer...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •