Results 1 to 7 of 7
  1. #1
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24

    Data in queries

    Hey guys,

    When we design a query, that contains info from 3 tables, say :Order, Salestocustomer and CustomerSales. All the data is related, but when we run a query, do we need the same data to be in all the tables?

    Like if we have one order that has qty 5 and in the saletocustomer has 5 qty but in customersales has no record of it, why doesn't it show it the query?

    my question is do we need to have a record of each info even if not sold to run a query that has info from the 3 tables in it?

    It is also possible that I am not able to drive my idea home, so here is the ex:

    Order: Icecream 5 box
    Salestocustomer: Icecream5box
    Customersales: no record for ice cream.



    when I run a query and add: qty from order and salestocustomer and customersale. why doesn't show?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By default an INNER join is created, which requires a record in each table. You can right or double click on the join line to change that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Without knowing what those tables mean and how they are related it is hard to tell. Can you post a copy of your database?

  4. #4
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    test.ziptest.zipHello,

    Ok here is what I mean.

    I want to run a querry that has all the quantity from quotation, orders and sales. yet when I add the quantity from the 3 tables missing is showing....
    How to make the database in a way that no matter which data I add on any table it shows anyway the total of the quantity

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I see several challenges here. Because the three tables all contain different combinations of customers and products, inner joins do not work. You will then only see the combinations that occur in all three tables. You should be working with outer joins. With three tables it gets very complicated. In addition, it is sometimes the case that a combination of customer and article can appear several times in one table and only appear once in another. You probably only want to see that combination once. That means you have to total the quantities first. So this requires quite a complicated solution. I'm not sure yet. I'll try some more later.

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    If you can upload a screenshot of what you expect to see then it would help.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I hope this query will do the job
    Code:
    SELECT CustName, Name, SumQuatations, SumOrders, SumSales
    FROM (((
    (SELECT CustProd.CustID, CustProd.ProductID, SumQuatations 
    FROM (SELECT CustID, ProductID FROM Quotation UNION SELECT CustID, ProductID FROM Orders UNION SELECT CustID, ProductID FROM Sales) AS CustProd 
    LEFT JOIN (SELECT Customer.CustID, TblProducts.ProductID, Sum(Quantity) AS SumQuatations 
    FROM TblProducts INNER JOIN (Customer INNER JOIN Quotation ON Customer.CustID = Quotation.CustID) ON TblProducts.ProductID = Quotation.ProductID 
    GROUP BY Customer.CustID, TblProducts.ProductID) AS SumQuotations 
    ON (CustProd.CustID = SumQuotations.CustID) AND (CustProd.ProductID = SumQuotations.ProductID)) QQ 
    INNER JOIN 
    (SELECT CustProd.CustID, CustProd.ProductID, SumOrders 
    FROM (SELECT CustID, ProductID FROM Quotation UNION SELECT CustID, ProductID FROM Orders UNION SELECT CustID, ProductID FROM Sales) AS CustProd 
    LEFT JOIN (SELECT Customer.CustID, TblProducts.ProductID, Sum(Quantity) AS SumOrders 
    FROM TblProducts INNER JOIN (Customer INNER JOIN Orders ON Customer.CustID = Orders.CustID) ON TblProducts.ProductID = Orders.ProductID 
    GROUP BY Customer.CustID, TblProducts.ProductID) AS SumOrders 
    ON (CustProd.CustID = SumOrders.CustID) AND (CustProd.ProductID = SumOrders.ProductID)) QO 
    ON (QQ.CustID = QO.CustID) AND (QQ.ProductID = QO.ProductID)) 
    INNER JOIN 
    (SELECT CustProd.CustID, CustProd.ProductID, SumSales 
    FROM (SELECT CustID, ProductID FROM Quotation UNION SELECT CustID, ProductID FROM Orders UNION SELECT CustID, ProductID FROM Sales) AS CustProd 
    LEFT JOIN (SELECT Customer.CustID, TblProducts.ProductID, Sum(Quantity) AS SumSales 
    FROM TblProducts INNER JOIN (Customer INNER JOIN Sales ON Customer.CustID = Sales.CustID) ON TblProducts.ProductID = Sales.ProductID 
    GROUP BY Customer.CustID, TblProducts.ProductID) AS SumSales 
    ON (CustProd.CustID = SumSales.CustID) AND (CustProd.ProductID = SumSales.ProductID)) QS 
    ON (QQ.CustID = QS.CustID) AND (QO.ProductID = QS.ProductID)) 
    INNER JOIN Customer ON QQ.CustID = Customer.CustID) 
    INNER JOIN TblProducts ON QQ.ProductID = TblProducts.ProductID;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report with data from 2 queries
    By Tuckejam in forum Reports
    Replies: 3
    Last Post: 01-10-2022, 10:35 AM
  2. Replies: 3
    Last Post: 09-11-2018, 12:40 PM
  3. Duplicate Data on Queries
    By spyldbrat in forum Access
    Replies: 4
    Last Post: 04-22-2016, 12:59 PM
  4. Six Queries and Merg Data
    By winterh in forum Queries
    Replies: 1
    Last Post: 08-06-2012, 03:23 PM
  5. Queries not returning data
    By Verso in forum Queries
    Replies: 1
    Last Post: 08-11-2011, 01:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums