Best practice in implementing a two-part unique identifier?
May 9, 2011 9:52 AM Subscribe
Best way to implement a two-part unique identifier in Access?
posted by jsturgill to computers & internet (14 answers total) 1 user marked this as a favorite
I've been tasked with building a solution that involves items with a two-part identifier that must be unique. Imagine the identifiers as letters and numbers. Each part can have multiple entries with the same values, but when put together, they must be unique.
I could make [LetterPart] and [NumberPart] together the primary key for the table. But I'd love to have a primary key that is just one entry, and still have the database reject any attempt to insert a non-unique [LetterPart] and [NumberPart] pair.
I'd also love for a way to have the database automatically increase the number part in a way that is aware of the letter part, so I don't have to do it in code. So I could tell it to Insert new AA, and it looks at the AA entries, sees the last one was 02, and creates AA03. Then when I tell it to insert CC, for example, it creats CC01.
Are these things possible? What are the known, sane approaches to this problem?