Search code examples
sqlms-accessms-access-2007

How do I create a subquery that sums a calculated column in Access 2007..?

How do I make the following query/subquery work..? Its purpose is to calculate the weight of an order. The query will then be used as a subquery in others, and joined on the invoice number. If possible, I would like to make this work without using a subquery/subselect in the SQL.

For clarity, I should state this query is in an Access 2007 "utility frontend" that I've created for an old and messy AS400 order management system on an IBM iSeries DB2 SQL server that I inherited through attrition, but these details probably aren't pertinent to the problem.

This dev version of the query works perfectly with an invoice number hard coded into it:

SELECT Sum([OrdersThisWeek].[Qty]*[Products].[Wgt]) AS InvWgt
FROM Products INNER JOIN OrdersThisWeek ON Products.Item = OrdersThisWeek.Item
WHERE OrdersThisWeek.InvNum=557618 AND OrdersThisWeek.Qty>0 AND Products.Wgt>0;

But as shown below, when I move invoice number from WHERE to SELECT, save it, drop it into another query, and join it on the invoice number, it causes the following error: "You tried to execute a query that does not include the specified expression 'InvNum' as part of an aggregate function.". I understand what it means, but I don't know what to do about it:

SELECT OrdersThisWeek.InvNum, Sum([OrdersThisWeek].[Qty]*[Products].[Wgt]) AS InvWgt
FROM Products INNER JOIN OrdersThisWeek ON Products.Item = OrdersThisWeek.Item
WHERE OrdersThisWeek.Qty>0 AND Products.Wgt>0;

I've played with it, Googled it, and read about it, but obviously there are some key concepts that I'm missing.

Solution

  • It means that, from the table OrdersThisWeek, if you want to calculate the weight of the items on invoice number 557618, you'll get something like:

     InvNum     InvWgt
 ------     ------
 557618     42

    But if you don't specify an invoice number, SQL doesn't know how to group the results. Do you just want one big sum of what every order ever weighs? Probably not. You want to use 'GROUP BY' in your statement like this:

    SELECT OrdersThisWeek.InvNum, Sum([OrdersThisWeek].[Qty]*[Products].[Wgt]) AS InvWgt
FROM Products INNER JOIN OrdersThisWeek ON Products.Item = OrdersThisWeek.Item
WHERE OrdersThisWeek.Qty>0 AND Products.Wgt>0
GROUP BY OrdersThisWeek.InvNum;