Create a new Id Number field

I’m a lowly novice Access user.

I created a database of our brain tumor patients. Up till now it’s just been a standalone database. So, using the autonumber from the primary key in my demographics table as the patient Id Number has worked well up till now. However now we are planning to add another site. If we pool our data, our patient ID numbers will overlap and will no longer be unique.

I need a way to easily distinguish my patients from patients at the other site. For example, my patient ID numbers would start with the letter “a”. Another site’s would start with the letter “b”. I don’t want to monkey with the autonumber in the primary key field because I’ve always heard that doing that can lead to problems.


So, since Access only allows one autonumber field per table, how do create a Id number field in my demographics table that is both unique to my site, and automatically increases with each record in the database like an autonumber?

Thanks

This is what you are looking for

About halfway down the page there is about 5 different examples.

Hope this helps,

Dan

How about creating a tblSite? You'll be able to use the PK from tblSite as a foreign key in your demographics table.

Cheers,