Results 1 to 2 of 2
  1. #1
    theevilsam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    19

    Query not showing all values... Maybe a join error, though can't see it....

    I have the following queries,
    Code:
      
    QryStockOnHand
    SELECT QrySaleTot.Item, QrySaleTot.ProductID, [QryStockLevel].[Stock]-[QrySaleTot].[Quantity] AS StockOnHand
    FROM QryStockLevel INNER JOIN QrySaleTot ON QryStockLevel.ProductID = QrySaleTot.ProductID;
    
    QrySaleTot
    SELECT TblProduct.Item, Sum(TblTotalSale.Size) AS Quantity, TblProduct.ProductID
    FROM TblProduct INNER JOIN TblTotalSale ON TblProduct.[ProductID] = TblTotalSale.[ProductID]
    GROUP BY TblProduct.Item, TblProduct.ProductID;
    
    QryStockLevel
    SELECT TblStock.ProductID, Sum(TblStock.StockLevel) AS Stock, TblProduct.Item
    FROM TblStock INNER JOIN TblProduct ON TblStock.ProductID = TblProduct.ProductID
    GROUP BY TblStock.ProductID, TblProduct.Item;
    When I run the QryStockonHand and no sales of a product have been made then the porduct does not appear in the result of the query...
    Sample Data


    TblStock
    StockID ProductID StockLevel
    138 1 528
    139 3 528
    140 5 528
    141 9 528
    142 7 528
    143 18 80
    144 30 72
    145 34 72
    146 33 72
    147 32 200
    148 22 80
    149 19 80
    150 23 80
    151 20 80



    TblProduct
    ProductID Item Price StockDelivery PriceSmall Large Small
    1 Carling £2.50 528 £1.40 2 1
    3 Carlsburg £2.70 528 £1.60 2 1


    5 IPA £2.30 528 £1.20 2 1
    7 StrongBow £2.80 528 £1.65 2 1
    9 RevJames £2.45 528 £1.30 2 1
    11 Becks £2.90 72 1
    12 WKDBlue £2.80 72 1
    13 WKDRed £2.80 72 1
    14 SmirnoffIce £2.80 72 1



    TblTotalSale
    TotalSalesID ProductID SalePrice Day Time Size
    576 1 £1.40 19/02/2012 15:34:24 1
    528 1 £2.50 09/02/2012 14:44:44 2
    530 1 £1.40 09/02/2012 14:44:44 1
    565 1 £2.50 19/02/2012 15:34:24 2
    567 1 £1.40 19/02/2012 15:34:24 1
    570 3 £2.70 19/02/2012 15:34:24 2
    571 3 £1.60 19/02/2012 15:34:24 1
    577 3 £2.70 19/02/2012 15:34:24 2
    578 3 £1.60 19/02/2012 15:34:24 1
    533 3 £2.70 09/02/2012 14:44:44 2
    534 3 £1.60 09/02/2012 14:44:44 1



    Any Idea why... I guess it is a null thing, where it is seeing the no sales as a non existent thing, instead of a zero sales.... any idea how i could fix it?
    Thanks
    Sam

  2. #2
    theevilsam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    19
    Fixed it with the following...

    Code:
    SELECT QryStockLevel.Item, QryStockLevel.ProductID, [QryStockLevel].[Stock]-NZ([QrySaleTot].[Quantity],0) AS StockOnHand
    FROM QryStockLevel LEFT OUTER JOIN QrySaleTot ON QryStockLevel.ProductID = QrySaleTot.ProductID;

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

Similar Threads

  1. showing all values in query
    By dashingirish in forum Queries
    Replies: 13
    Last Post: 02-17-2012, 04:38 PM
  2. Showing zero values in a Union Query
    By coach32 in forum Queries
    Replies: 5
    Last Post: 09-06-2011, 07:46 AM
  3. Outer left join leaves out null values
    By mschles4695 in forum Queries
    Replies: 1
    Last Post: 12-22-2010, 11:43 PM
  4. Sql query not showing all the values
    By usr123 in forum Access
    Replies: 0
    Last Post: 02-24-2010, 07:32 AM
  5. Null Values not showing up in a Query
    By Valli in forum Queries
    Replies: 0
    Last Post: 01-04-2006, 03:53 PM

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