Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006

    Unanswered: Query Joins - subquery and counts


    I have a query that I want to create in a new Access application that I am a bit stumped on. It will contain a main table with the item numbers, 3 tables where I need a count of the amount of times the item number appears, and a table with the item number and a second column with a value I need to pull in. I am trying to accomplish this in a single query and am fairly sure I will need the use of subqueries. My use of subqueries is growing but still fairly limited. I believe I know how to write the sbuquery to join one of the tables to the main but unsure of how to fit subsequent tables in. This is where I turn to our lovely community here, HELP! The table formats are below

    Item (Primary Key, this is the field all other tables will need to join with)
    * Other fields that are irrelevant

    Item (this is the field I would need to count)

    Item (this is the field I would need to count)

    Item (this is the field I would need to count)

    Item (Primary Key)
    Excess (this is the field I would need to retrieve the value from)

    To summarize I would need the data to appear as follows

    Item __ In Hold __ In Prod __ In Inv __ Excess
    X __ 1__ 2 __ 5 __ 0
    Y __ 0 __ 4 __ 27 __ 3

    Those are random numbers I just through in.

    I need all items from tblItems displayed and a count of how many time each item appears in the Item field of the three kanban tables. Finally I need to pull the value of Excess from tblExcess for each item number.

    Anybody otu there that can give a hand?

  2. #2
    Join Date
    Feb 2006


    Allright everyone,

    I wanted to let you know I think I solved the problem. Somehow it finally started to make sense until I came out with the following:

    SELECT tblItems.Item, IIf(tblExcessOrdersInProduction.ExcessOrders Is Null,0,tblExcessOrdersInProduction.ExcessOrders) AS ExcessOrdersInProduction, Q2.KanbanInInv, Q3.KanbanInHold, Q4.KanbanInProduction
    FROM (((tblItems LEFT JOIN tblExcessOrdersInProduction ON tblItems.Item = tblExcessOrdersInProduction.Item) LEFT JOIN [SELECT tblItems.Item, Count(tblKanbanInInv.Item) AS KanbanInInv
    FROM tblItems LEFT JOIN tblKanbanInInv ON tblItems.Item = tblKanbanInInv.Item
    GROUP BY tblItems.Item]. AS Q2 ON tblItems.Item = Q2.Item) LEFT JOIN [SELECT tblItems.Item, Count(tblKanbanInHold.Item) AS KanbanInHold
    FROM tblItems LEFT JOIN tblKanbanInHold ON tblItems.Item = tblKanbanInHold.Item
    GROUP BY tblItems.Item]. AS Q3 ON tblItems.Item = Q3.Item) LEFT JOIN [SELECT tblItems.Item, Count(tblKanbanInProduction.Item) AS KanbanInProduction
    FROM tblItems LEFT JOIN tblKanbanInProduction ON tblItems.Item = tblKanbanInProduction.Item
    GROUP BY tblItems.Item]. AS Q4 ON tblItems.Item = Q4.Item;
    I hope that helps anyone else that runs into this.


  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by wrestleSBA
    it finally started to make sense until
    until? LOL

    thanks for the update

    that's sure a lot of LEFT JOINing and GROUP BYing | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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