Hello World ;)

I've found great help in many WROX forums, so I hope you will all help me again. I really appreciate your efforts and knowledge.

I have 3 tables, and in one of the tables I need to have the PK be composed of a FK from an other table and a value that is incremented by one.

tblSite:
    SiteID, nvarchar(4)
    SiteName nvarchar(30)

tblOrder
    OrderID smallint
    OrderDate smalldatetime
    SiteID nvarchar(4)
...
...
...

tblOrderRequisition
    OrderRegID smallint
    OrderID smallint
    ReqID smallint

tblRequisition
    ReqID smallint
    ItemID smallint
...
...
...

Obviously these tables are related, now my question is:
Can I change my database so that the ReqID can be a composite of SiteID and a value that is incremented by one for each record in the tblRequisition? And how do I do that?

I'm somewhat experienced in Access, and here I would have used a lookup, but how do you perform a cross table lookup in SQL Server 2005?

Please be very specific in your reply, I am as mentioned a little new to designing SQL Server 2005 databases.

Again thanks in advance for your help.

Kind regards
Tina Nielsen
Denmark


Kind regards
Tina Nielsen, Denmark