steve whenever i have an overall status that depends on the details, i do not store it in the parent/Orders table. I always try to calculate it based on the details, for example.

I create a view instead, and the view calculates the status, so it is automatically correct based on the details.

an example might be something like this: notice the case statement and the group by sub select to figure it all out:

CREATE TABLE Orders (

OrderID int identity(1,1) not null primary key,

DateOrdered,

CustomerNumber)

CREATE TABLE OrderDetails(

DetailsID int identity(1,1) not null primary key,

OrderID int references Orders(OrderID)

ProductOrdered varchar(30)

ProductPrice decimal(19,4)

Shipped char(1) default('N') )

CREATE VIEW VW_ORDERS AS

SELECT

Orders.OrderID,

Orders.DateOrdered,

Orders.CustomerNumber

CASE

WHEN DETAILS.SHIPPEDCOUNT = DETAILS.TOTALCOUNT

THEN 'Shipped'

WHEN DETAILS.SHIPPEDCOUNT =0

THEN 'Not Shipped'

WHEN DETAILS.SHIPPEDCOUNT < DETAILS.TOTALCOUNT

THEN 'Partial Shipment'

END As Status

FROM VW_ORDERS

LEFT OUTER JOIN

(SELECT OrderID,

SUM(CASE WHEN Shipped = 'Y' THEN 1 ELSE 0 END) AS SHIPPEDCOUNT,

COUNT(DetailsID) AS TOTALCOUNT

FROM OrderDetails

GROUP BY OrderID) DETAILS

ON OrderDetails.OrderId = DETAILS.OrderID