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!

BenHow 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;|||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|||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;|||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