Increment field based on another field 1

[skyline666]

Hi,

I have a table called nRisk in a database in a Access 2003 Project, linked on SQL Server 2005. I have two fields in nRisk called RiskID and ProjectID. RiskID is a primary key field. Want I need is to have RiskID act like an autonumber field, but start again after each new ProjectID. So for example the first ProjectID is 1, then there RiskID goes from 1 to 8 for the 8 risks, then the next ProjectID is 2, and then the RiskID starts at 1 again. As RiskID is a primary key field, then to have no duplicate records the ProjectID can be added to the RiskID (for example 1-1, 1-2, 2-5 (ProjectID-RiskID)). If it helps, then the ProjectID can go before the RiskID field if the RiskID is going to be based on ProjectID.

Many thanks in advance,

Andrew

 

[Remou]

There are several SQL Server fora, forum183 for example. You my find them useful.

 

[skyline666]

PS. I was being stupid when I said I couldn't find the indexes, i was looking in design view of the Form, not Table lol.

Have a nice day, and thanks again for your help.