For simplicity I have 2 tables: Orders = Date, OrdNum, ItemNum, TotQty

OrderDetails = OrdNum, ItemNum, OrdQty

(I did not create these tables.)Linking fields are OrdNum and ItemNum

There is 1 record in Orders for each OrdNum/ItemNum

There are zero to many records in OrderDetails for each OrdNum/ItemNum.

The sum of OrdQty in all OrderDetails should = the TotQty of the corresponding Order rec.

I want to find all Orders in a specified date range where no OrderDetails recs exist yet, or the sum of OrdQty <> TotQty.

This should be easy. I got the ol' invalid column when I tried to use the summed column alias in the where clause and I can't get the subset workaround to work because of the link. I'm stuck. Any help would be appreciaited. Thanks.

To find Orders with no Order details SELECT Orders.OrderNum

FROM Orders LEFT JOIN OrderDetails ON Orders.[OrderNum] = OrderDetails.[OrderNum]

WHERE (((OrderDetails.OrderNum) Is Null));

Thank you, but my real problem is with the sum of OrdQty. I need something with "where "sum(OrdQty) <> TotQty". That would find Orders with no OrderDetails as well as those with OrderDetails that don't add up. Any ideas? Thanks.

SELECT Orders.OrderNum, Sum(OrderDetails.OrdQty) AS SumOfOrdQty

FROM Orders LEFT JOIN OrderDetails ON Orders.OrderNum = OrderDetails.OrderNum

GROUP BY Orders.OrderNum, OrderDetails.OrderNum, OrderDetails.OrdQty, Orders.TotQty

HAVING (((OrderDetails.OrderNum) Is Null)) OR (((Sum(OrderDetails.OrdQty))<>[Orders].[TotQty]));

MDOW, thank you so much. That's just what I needed. It makes perfect sense now that I see it and I will be able to build on that to make what I need.

Ask Your Question

Weekly Poll

Would you be willing to go on a SpaceX trip around the moon?

Discuss in The Lounge

Poll History