>>>>Hi Naomi
>>>>Thanks for helping. I am using version 2005.
>>>>Yes, it's one Activity record for each date/tour combination.
>>>>Changing Dates or TourID is allowed in the Orders table, that's why made things too complicated for me.
>>>>If an activity record has lost all his reservations, it can be deleted...
>>>>
>>>>Jaime
>>>>
>>>
>>>I see - then it's more complex indeed and perhaps it may be better solved with a trigger (using UPDATE check).
>>>
>>>I need to run now, post your attempts in the meantime and I'll help you when I return.
>>
>>Hi Naomi
>>Checked triggers help. I agree the function should be triggered upon insert / update , but it should run the same code in all cases.
>>The way i see the function should be : (no matter if after insert or update):
>>1) looks up if specific date/tour combination already exists in the Activity table. If not found, we will use an INSERT command, otherwise, we will use an UPDATE.
>>2) generate the new Activity line, i.e select from Orders group by date/tour for specific date/tour
>>3) run the command from 1) with the data from 2)
>>Read a lot about triggers and stored procs, but still, i'm not at the level where i can do this all by myself :-(
>>
>>Jaime
>

>DECLARE @Activ table (ActivityID int, [Date] datetime, Tour_ID int)
>IF OBJECT_ID('TempDB..#TempActiv',N'U') IS NOT NULL DROP TABLE #TempActiv
>
>BEGIN TRANSACTION
>-- Insert or update order - assume we know the @OrderID
>select [Date],[Tour_ID], Sum(Pax) as TotalPax INTO #TempActiv from dbo.Orders 
>WHERE OrderID = @OrderID
>GROUP BY  [Date],Tour_ID
>
>UPDATE Activity SET TotalPax = TA.TotalPax from dbo.Activity INNER JOIN #TempActiv TA ON Activity.[Date] = TA.[Date]
>AND Activity.[Tour_ID] = TA.[Tour_ID]
>OUTPUT Inserted.Activity_ID, Inserted.[Date], Inserted.[Tour_ID] INTO @Activ
>IF @@ROWCOUNT = 0 -- this is new date/tour_id
>   INSERT INTO Activity ([Date],[Tour_ID], TotalPax)
>   OUTPUT Inserted.ActivityID, Inserted.[Date], Inserted.[Tour_ID] into @Activ
>  SELECT * from #TempActiv
> 
>UPDATE Orders  SET Activity_ID = A.Activity_ID from dbo.Orders INNER JOIN @Activ A
>ON Orders.[Date] = A.[Date] and Orders.[Tour_ID] = A.[Tour_ID]
>WHERE Orders.OrderID = @OrderID -- update the new order/changed order with the new activityid
>END TRANSACTION

>
>This is the idea of this SP. I suggest you to also read this blog post by Alex Kuznetsov
>Patterns that don't work as expected for some ideas of improving the above scheme. (Read comments to this blog).

Naomi
First thanks! I'll try to digest.
Second, all i have to say is "Ima'le! "
Last, and not least, Happy Passover :-)

Jaime