Calculating Unique Catalog #

[itsalljustaride]

Hello,

I apologize if this has already been discussed here, but a search didn't turn up anything of use.

I have a database that I am using to catalog and track foreign language media checkouts. Each title has a catalog # of the form AA-## where the first letter is the language it is in (A for Arabic, etc), the second letter is the first letter in the title (A for Amelie for example), and the last part is a unique two-digit number that we simply increment by 1 when we get another "AA-##" title.

For instance, say I have 5 titles with cat #s "AA-". The last one would have a cat # of "AA-05". If I wanted to add another Arabic film whose title started with "A" then I would need it to automatically assign it the cat # "AA-06".

I have a calculation set up to assign the catalog number based on the language and title, which gets me the first two letters, but I can't figure out how to begin getting the unique two-digit number at the end. What functions would I use?

The cat #s are indexed, so if there is a way to search for all the cat #s which match, find the one with the highest ending number, then increment by 1? Maybe that's more work than is needed.

Any advice? Thanks.

[comment]

It has been discussed many times, the last time being:

http://www.fmforums.com/forum/showtopic.php?tid/191695/

[itsalljustaride]

Thanks for the point in the right direction. I think I see what needs to be done, vaguely.