First of all, I would keep the mainkey and the attachment number in separate fields.
If there is no need for the end-user to see the attachment number, you could use a single sequence for all attachments:
MainKey Attachment
1 1
2 2
1 3
3 4
This fulfils the condition that the attachment has a unique number. It is easier to create this sort of sequences.
If for any reason you don't like this, you will have to check the highest attachment number for a given MainKey:
select max(attachment) as maximum;
from MyTable;
where MainKey = lnKey;
into cursor Temp
? Temp.Maximum
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
