Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004

    Angry Unanswered: calculations within a query

    I have a stock database in which i store sales in an order transaction table order_Details and purchases in purchase_details and all products in a table called products with a key field productID. I wish to query all products in the product table and display the qty purchased - qty sold. This works but does not occur if a purchase or sale has been made for that product. Can someone help with the query design

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    If you REALLY want to do this at the query level, you have two options, DSum or subqueries.

    DSum might look like this:

    SELECT product, product_id, DSum("qty_purchased", "purchase_details", "product_id = " & product_id)

    Subquery might look like this:

    SELECT tbl.product, tbl.product_id, (SELECT SUM(qty_purchased) FROM purchase_details WHERE purchase_details.product_id = tbl.product_id)
    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