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

>>Hi
>>i have the following tables: for readability i fill it with data so it's easy to understand what's needed
>>Orders Table
>>

>>id  date              tourid    pax     ActivityID
>>1   01/04/2012      1          2           1
>>2   01/04/2012      2          1           2
>>3   01/04/2012      1          1           1
>>4   02/04/2012      1          1           3

>>Then i would like to maintain an Activity table, which is the Orders table grouped by Date and TourID as in:
>>Activity table:
>>

>>ActivityID    Date           tourid     TotalOrders    TotalPax
>>    1          01/04/2012      1                3              4
>>    2          01/04/2012      2                1              1
>>    3          02/04/2012      1                1              1

>>
>>I would like a stored procedure to do the following: when adding a new Order, either create a new activityID line, or update the quantities if there already is one. When modifying an order, modify the resuting ActivityiD accordingly. Finally, i would like to save the related ActivityID in the orders table.
>>
>>Thanks
>>Jaime
>
>I'm not sure I understand. Do you want to maintain 1 Activity line for each date for the same TourId? Also, it's a bit complicated relationship . Also, what happens if you change Order date?
>
>In any case, it's not hard to write a stored procedure that will fit your requirements (if I understood activity table correctly - 1 row per date/TourId).
>
>What is your SQL Server version?