linking tables/summed field in where clause

May 19, 2010 at 11:12:57
Specs: dos
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.

See More: linking tables/summed field in where clause

Report •

May 19, 2010 at 16:19:45
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));

Report •

May 23, 2010 at 06:26:30
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.

Report •

May 25, 2010 at 11:55:02
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]));

Report •

Related Solutions

May 25, 2010 at 13:39:14
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.

Report •

June 29, 2010 at 15:00:25
All of these informations are new for me, thanks to oyu.

Apartment Software

Report •

Ask Question