Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Answered: Query with Sum, Left Join and Count

    Hi All,
    I suspect this is going to be easier that I think, but I cannot get my head around why I can get this to work...

    I have a database recording deliveries from various suppliers recording (among others) unique id, order number, delivery docket, supplier and product in one table (tblUnitDely) and unique id, Serial number for any faulty units from deliveries in another table (tblUnitFailures)

    There are often various deliveries against each purchase order as below
    Click image for larger version. 

Name:	unit dely.PNG 
Views:	5 
Size:	8.5 KB 
ID:	16853
    I'm trying to create a query to summarise deliveries - essentially for each order I want to show supplier, stock code, qty delivered (so summing quantities on a purchase order, ignoring the delivery docket) and then listing the qty faulty against each purchase order

    I can happily create this for supplier, stock code and qty delivered with the following access query which gives the correct results
    Click image for larger version. 

Name:	work query.png 
Views:	1 
Size:	5.2 KB 
ID:	16852

    However, when I add in what I think is the correct approach to count the number of faulty units against each purchase order, the previously correct summed 'quantity delivered' values are incorrect as is the quantity faulty value.
    Below is the msaccess query screen
    Click image for larger version. 

Name:	not working.PNG 
Views:	5 
Size:	15.5 KB 
ID:	16854

    Can anyone shed some light on what I am doing wrong?

    Many thanks,
    Richard.

  2. Best Answer
    Posted by healdem

    "Sorry I don't use the query designer, except when forming joins...
    In conventional SQL youd probably be best SUMming the deliveries and tge failures as separate sub selects within a top level query that identified unique products.
    But for this in Access youd be better off having a top level query that joins to a SUMming query as opposed to the sub selects

    So sub query 1 sums all deliveries by product code
    Sub query 2 sums all failures by product code
    Then insert these queries into another query which identifies is it po number and supplier"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry I don't use the query designer, except when forming joins...
    In conventional SQL youd probably be best SUMming the deliveries and tge failures as separate sub selects within a top level query that identified unique products.
    But for this in Access youd be better off having a top level query that joins to a SUMming query as opposed to the sub selects

    So sub query 1 sums all deliveries by product code
    Sub query 2 sums all failures by product code
    Then insert these queries into another query which identifies is it po number and supplier
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Apr 2016
    Posts
    2
    Quote Originally Posted by healdem View Post
    Sorry I don't use the query designer, except when forming joins...
    In conventional SQL youd probably be best SUMming the deliveries and tge failures as separate sub selects within a top level query that identified unique products.
    But for this in Access youd be better off having a top level query that joins to a SUMming query as opposed to the sub selects

    So sub query 1 sums all deliveries by product code
    Sub query 2 sums all failures by product code
    Then insert these queries into another query which identifies is it po number and supplier
    Thanks, that got it sorted.
    Very much appreciated

    Richard.

Posting Permissions

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