Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Multiple Table Query

    I'm designing a tracking system to track returnable containers, I am having trouble with a query to pull all the totals of what we have shipped and recieved. With multiple tables involved, I'd like it to eventually show inventories of containers at our outsource companies. Can I make this happen using one query?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Learn sql.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Do you want like CompanyA has 14 containers? and say you have types of containers split up. Such as TotalContainers: [NameOfField]+[NameOfField2]
    Put that statement in the field in the query build design window.

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Dumb Response

    First off, Teddy, why would you post something as useless as that "Learn SQL". People come here to learn not to be belittled.

    Secondly, your answer

    What you are probably going to want to do is make a union query (it is hard to guess with out knowing more information)

    I would assume it might be something like this.

    SELECT "Shipped" AS col1, COUNT(containers), facility FROM tblshipped GROUP BY facility
    UNION
    SELECT "Received" as col1, COUNT(containers), facility FROM tblReceived GROUP BY facility


    This will give you total recieved and total shipped, then you can do whatever you want with this data.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by mjweyland
    First off, Teddy, why would you post something as useless as that "Learn SQL". People come here to learn not to be belittled.
    Yeah I'm sorry, that was pretty harsh...

    I'm curious as to what your db schema is though. I get the idea that you could solve a lot of problems by re-thinking the logic behind your database.

    Sorry for being an ass... bad day...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2004
    Posts
    3

    Thumbs up

    Thanks fellas.... I'm relatively new to Access, 2-6 weeks courses are almost behind me now. For more of a broadband view of this project...
    Daily entries will be make to roughly 14 different companies. With 2-8 different types of containers going to each one. What I would like to be available is a specific container count, along with a total count for each company. Right now the data for shipped, received, and starting container inventory are all separate tables.

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I think this will be something like I said...

  8. #8
    Join Date
    Jul 2004
    Posts
    3
    How would I subtract the incoming containers from the companies inventory..how do I total this information?...here's what I have so far....

    SELECT QTY FROM STARTING_OUTSOURCE_INVENTORY WHERE COMPANY_ID=1 AND CONTAINER='A3'
    UNION ALL
    SELECT SHIP_QTY FROM OUTBOUND_LTI_OUTSOURCE WHERE COMPANY_ID=1 AND SHIP_CONTAINER='A3';

    ......guidance please!

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Morgan78
    Thanks fellas.... I'm relatively new to Access, 2-6 weeks courses are almost behind me now. For more of a broadband view of this project...
    Daily entries will be make to roughly 14 different companies. With 2-8 different types of containers going to each one. What I would like to be available is a specific container count, along with a total count for each company. Right now the data for shipped, received, and starting container inventory are all separate tables.
    I'm still having difficulty wrapping my head around your table structure.

    Could you please post your tables, and the fields they consist of?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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