Filling a field from a primary key

[Guest]

I have a primary key that is based on SSN's. I have an external system that
uses the last five digits for a ID number. I need to have the ID for the
external system (which is a field in my table) to populate automatically
from the primary key.

Any suggestions would be appreciated.

 

[George Walsh]

If you have a field named SSN in a table which contains the Social Security
Number (say it does not include dashes, just digits - but it is formatted as
a string rather than a number), and you want another field in your table to
automatically populate with just the rightmost five digits from the Social
Security Number field, then you could design and use a query which is based
on your table, and use the calculated field method to create a field named
"ID Number" with the formula: ID Number: =
Right$([MyMainTableName]![SSN],5). Design your forms, reports, etc. using
the Query rather than the Table, and the (separate) automatically calculated
"ID Number" field will be available for every record.

The logic is to minamalize the fields of data you actually store in a
table - especially when one field can be derived from another and is not an
absolutely unique piece of information relating to the subject of the table.

 

[George Walsh]

If you have a field named SSN in a table which contains the Social Security
Number (say it does not include dashes, just digits - but it is formatted as
a string rather than a number), and you want another field in your table to
automatically populate with just the rightmost five digits from the Social
Security Number field, then you could design and use a query which is based
on your table, and use the calculated field method to create a field named
"ID Number" with the formula: ID Number: =
Right$([MyMainTableName]![SSN],5). Design your forms, reports, etc. using
the Query rather than the Table, and the (separate) automatically calculated
"ID Number" field will be available for every record.

The logic is to minamalize the fields of data you actually store in a
table - especially when one field can be derived from another and is not an
absolutely unique piece of information relating to the subject of the table.

 

[Guest]

Thank you. That worked great.

If you have a field named SSN in a table which contains the Social Security
Number (say it does not include dashes, just digits - but it is formatted as
a string rather than a number), and you want another field in your table to
automatically populate with just the rightmost five digits from the Social
Security Number field, then you could design and use a query which is based
on your table, and use the calculated field method to create a field named
"ID Number" with the formula: ID Number: =
Right$([MyMainTableName]![SSN],5). Design your forms, reports, etc. using
the Query rather than the Table, and the (separate) automatically calculated
"ID Number" field will be available for every record.

The logic is to minamalize the fields of data you actually store in a
table - especially when one field can be derived from another and is not an
absolutely unique piece of information relating to the subject of the table.

I have a primary key that is based on SSN's. I have an external system
that
uses the last five digits for a ID number. I need to have the ID for the
external system (which is a field in my table) to populate automatically
from the primary key.

Any suggestions would be appreciated.