Primary Key Default Value

[Guest]

I’m creating a patients database. I’m using the patients social security number as the primary key in the patients table. Now I’m creating a table which will contain all of the appointments of all patients. Since obviously most patients will have more than one appointment sooner or later I want the primary key to be the social security number followed by something else [XXX-XX-XXXX-1; XXX-XX-XXXX-2, etc. or something along those lines]. Is it possible to some how make the table assign this number automatically? Like for example creating a code on the Expression Builder to call the patients social security number and if it’s not found add it to the table with a primary key of XXX-XX-XXXX-1, or if its found count how many times and increment that number by 1? 1 record found XXX-XX-XXXX-2, 2 records found XXX-XX-XXXX-3, etc.

Thanks.

 

[Brendan Reynolds]

Not at the table level, no. It would be possible to do something like this
using forms and VBA, but it can get quite complex, and my advice would be
not to do that, but to make the primary key the combination of the social
security number and the date/time of the appointment instead.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I'm creating a patients database. I'm using the patients social security

number as the primary key in the patients table. Now I'm creating a table
which will contain all of the appointments of all patients. Since obviously
most patients will have more than one appointment sooner or later I want the
primary key to be the social security number followed by something else
[XXX-XX-XXXX-1; XXX-XX-XXXX-2, etc. or something along those lines]. Is it
possible to some how make the table assign this number automatically? Like
for example creating a code on the Expression Builder to call the patients
social security number and if it's not found add it to the table with a
primary key of XXX-XX-XXXX-1, or if its found count how many times and
increment that number by 1? 1 record found XXX-XX-XXXX-2, 2 records found
XXX-XX-XXXX-3, etc.

 

[Rick Brandt]

I'm creating a patients database. I'm using the patients social security

number as the primary key in the patients table. Now I'm creating a table
which will contain all of the appointments of all patients. Since obviously
most patients will have more than one appointment sooner or later I want
the primary key to be the social security number followed by something else
[XXX-XX-XXXX-1; XXX-XX-XXXX-2, etc. or something along those lines]. Is it
possible to some how make the table assign this number automatically? Like
for example creating a code on the Expression Builder to call the patients
social security number and if it's not found add it to the table with a
primary key of XXX-XX-XXXX-1, or if its found count how many times and
increment that number by 1? 1 record found XXX-XX-XXXX-2, 2 records found
XXX-XX-XXXX-3, etc.

You don't need (or want) to combine this into one field. Just use a PK
that consists of both fields. This is called a composite PK. All you have
to do is select both fields in the design view of the table and then press
the "key" button in the toolbar.

 

[JohnFol]

It sounds like you have 1 entity (ie the person) who can be uniquely
identified by their SSN. It also sounds like the person can eb a patient on
many occasions. I would then argue the SSN is the Foreign key in the
Appointments table, possibly as part of a PK, but NOT a key in it's own
right.

I'm creating a patients database. I'm using the patients social security

number as the primary key in the patients table. Now I'm creating a table
which will contain all of the appointments of all patients. Since obviously
most patients will have more than one appointment sooner or later I want the
primary key to be the social security number followed by something else
[XXX-XX-XXXX-1; XXX-XX-XXXX-2, etc. or something along those lines]. Is it
possible to some how make the table assign this number automatically? Like
for example creating a code on the Expression Builder to call the patients
social security number and if it's not found add it to the table with a
primary key of XXX-XX-XXXX-1, or if its found count how many times and
increment that number by 1? 1 record found XXX-XX-XXXX-2, 2 records found
XXX-XX-XXXX-3, etc.