fill in field based on another field

[Guest]

Hi, I am attempting to create a table with the Client ID field based on the
last 4 digits of SSN, 2 digit date and auto number. The table has the SSN,
autonumber fields.
In a query i am able to accomplish this by the following expression.
Right([ssinumber],4) & "-" & Right(Year(Now()),2) & [ID].
How do accomplish the same in the table or is there some type of update
query i must write?

 

[Rick B]

You typically don't store calculated values in a table. When you need that
calculated field, just create it in your query. Anything you can do with a
form can also be done with your query.

 

[Guest]

Thanks Rick,

But it is not a calculated value. I want it to look at the SSN filed and
Auto Field

You typically don't store calculated values in a table. When you need that
calculated field, just create it in your query. Anything you can do with a
form can also be done with your query.

--
Rick B

Hi, I am attempting to create a table with the Client ID field based on
the
last 4 digits of SSN, 2 digit date and auto number. The table has the
SSN,
autonumber fields.
In a query i am able to accomplish this by the following expression.
Right([ssinumber],4) & "-" & Right(Year(Now()),2) & [ID].
How do accomplish the same in the table or is there some type of update
query i must write?

 

[Jeff Boyce]

Access tables don't have a mechanism for doing this. Rick's response was
on-point -- you ARE attempting a "calculation" (by trying to concatenate the
values).

You generally don't need to. Instead, as Rick suggested, create a query in
which you do that concatenation.

Besides, it's not a good idea to try to store more than one fact in one
field, and your "calculation" would be storing three facts in one field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Okay, thanks for the clarity. I was not aware that the expression was
considered a calculation. So I guess I was "on-point" when i created the
query to do this. Now based on your last para. What would you suggest as a
method to acheive the client id based on the last 4 ssn, 2 digit year and
auto id number?

Access tables don't have a mechanism for doing this. Rick's response was
on-point -- you ARE attempting a "calculation" (by trying to concatenate the
values).

You generally don't need to. Instead, as Rick suggested, create a query in
which you do that concatenation.

Besides, it's not a good idea to try to store more than one fact in one
field, and your "calculation" would be storing three facts in one field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am attempting to create a table with the Client ID field based on
the
last 4 digits of SSN, 2 digit date and auto number. The table has the
SSN,
autonumber fields.
In a query i am able to accomplish this by the following expression.
Right([ssinumber],4) & "-" & Right(Year(Now()),2) & [ID].
How do accomplish the same in the table or is there some type of update
query i must write?

 

[Jeff Boyce]

I'm confused. It sounds like you already have a query that does this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, thanks for the clarity. I was not aware that the expression was
considered a calculation. So I guess I was "on-point" when i created the
query to do this. Now based on your last para. What would you suggest as
a
method to acheive the client id based on the last 4 ssn, 2 digit year and
auto id number?

Access tables don't have a mechanism for doing this. Rick's response was
on-point -- you ARE attempting a "calculation" (by trying to concatenate
the
values).

You generally don't need to. Instead, as Rick suggested, create a query
in
which you do that concatenation.

Besides, it's not a good idea to try to store more than one fact in one
field, and your "calculation" would be storing three facts in one field.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"LotsOfAttentionNeeded" <[email protected]>
wrote in message

Hi, I am attempting to create a table with the Client ID field based on
the
last 4 digits of SSN, 2 digit date and auto number. The table has the
SSN,
autonumber fields.
In a query i am able to accomplish this by the following expression.
Right([ssinumber],4) & "-" & Right(Year(Now()),2) & [ID].
How do accomplish the same in the table or is there some type of update
query i must write?

 

[Guest]

Yes I do have the query (see my original post) I was trying to determine if
I could accomplish the same results of the query thorough an expression in a
table. You and Rick have answered my question that it is not possible that I
must do so in a query.

Now with knowing that I cannot do so (New Question Can I auto update the
client id field in the table based on the results from the query that creates
the client id number - when a new record is added. Sorry if i am confusing
you!

I'm confused. It sounds like you already have a query that does this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, thanks for the clarity. I was not aware that the expression was
considered a calculation. So I guess I was "on-point" when i created the
query to do this. Now based on your last para. What would you suggest as
a
method to acheive the client id based on the last 4 ssn, 2 digit year and
auto id number?

Access tables don't have a mechanism for doing this. Rick's response was
on-point -- you ARE attempting a "calculation" (by trying to concatenate
the
values).

You generally don't need to. Instead, as Rick suggested, create a query
in
which you do that concatenation.

Besides, it's not a good idea to try to store more than one fact in one
field, and your "calculation" would be storing three facts in one field.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"LotsOfAttentionNeeded" <[email protected]>
wrote in message
Hi, I am attempting to create a table with the Client ID field based on
the
last 4 digits of SSN, 2 digit date and auto number. The table has the
SSN,
autonumber fields.
In a query i am able to accomplish this by the following expression.
Right([ssinumber],4) & "-" & Right(Year(Now()),2) & [ID].
How do accomplish the same in the table or is there some type of update
query i must write?

 

[Jeff Boyce]

I'll recommend that you start a new thread with your new question. If you
leave it buried deep inside this thread, it probably won't get as many
"eyes" on it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes I do have the query (see my original post) I was trying to determine
if
I could accomplish the same results of the query thorough an expression in
a
table. You and Rick have answered my question that it is not possible
that I
must do so in a query.

Now with knowing that I cannot do so (New Question Can I auto update the
client id field in the table based on the results from the query that
creates
the client id number - when a new record is added. Sorry if i am confusing
you!

I'm confused. It sounds like you already have a query that does this.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"LotsOfAttentionNeeded" <[email protected]>
wrote in message

Okay, thanks for the clarity. I was not aware that the expression was
considered a calculation. So I guess I was "on-point" when i created
the
query to do this. Now based on your last para. What would you suggest
as
a
method to acheive the client id based on the last 4 ssn, 2 digit year
and
auto id number?

:

Access tables don't have a mechanism for doing this. Rick's response
was
on-point -- you ARE attempting a "calculation" (by trying to
concatenate
the
values).

You generally don't need to. Instead, as Rick suggested, create a
query
in
which you do that concatenation.

Besides, it's not a good idea to try to store more than one fact in
one
field, and your "calculation" would be storing three facts in one
field.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"LotsOfAttentionNeeded"
<[email protected]>
wrote in message
Hi, I am attempting to create a table with the Client ID field based
on
the
last 4 digits of SSN, 2 digit date and auto number. The table has
the
SSN,
autonumber fields.
In a query i am able to accomplish this by the following expression.
Right([ssinumber],4) & "-" & Right(Year(Now()),2) & [ID].
How do accomplish the same in the table or is there some type of
update
query i must write?