Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Query gurus please?

    Hi,

    I'm trying to put together a query which will combine a number of values from several database tables. Currently, there are two tables in question, one of products, and one of order details.

    tblProducts:
    ProductID
    Product_Category
    Product_Weight

    tblDetails:
    DetailID
    Order_ID (linked to an order profile table)
    Product_ID (linked to tblProducts.ProductID)
    Product_Category
    Detail_Quantity


    What I need to achieve is a query (or several queries) that will retrieve the total weight of a detail record (ie tblProducts.ProductWeight * tblDetails.Detail_Category) and then group this by Product_Category. What you end up with is the total weight of each category of product - this will be used to calculate freight costs.

    I can construct a query to retrieve total quantities by category, and total weight per detail row, but how can I combine these?

    Retrieve total weight per detail row:

    SELECT tblProducts.ProductID, tblProducts.Product_Weight, tblDetails.Detail_Quantity, (tblDetails.Detail_Quantity*tblProducts.Product_We ight) AS FreightWeight, tblDetails.DetailID, tblDetails.Order_ID
    FROM tblProducts INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
    WHERE (((tblDetails.Order_ID)= *xxx* ));

    Retrieve total number of products ordered by category

    SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity) AS TotalQuantity
    FROM tblDetails
    GROUP BY tblDetails.Product_Category;

    Any suggestions would be great!

    Ben

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query gurus please?

    How about:

    SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
    FROM tblDetails
    INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
    GROUP BY tblDetails.Product_Category;

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    Thanks Andrew,

    When I try to run this query, I get a Syntax error in the JOIN statement. I've tried recreating this (unfortunately in Access) and I get an error which I can't understand:

    'You tried to execute a query that does not include the specified expression 'Product_ID' as part of an aggregate function'

    This error is repeated for any fields included, other than those in the SUM function. Is this just due to wiggy JetSQL? Your query structure makes sense to me, but I can't make it work.

    Any other ideas?

    Thanks again,

    Ben

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, that doesn't make sense to me. I presume you fixed the error in my code?

    SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
    FROM tblDetails
    INNER JOIN tblProducts ON tblProducts.ProductID = tblDetails.Product_ID
    GROUP BY tblDetails.Product_Category;

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    Andrew - you're a legend!

    I thought I had fixed that error, but I succeeded only in compounding the problem. This query of yours works a treat. Thanks very much.

    All the best,

    Ben

Posting Permissions

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