Name to Initials in table

  • Thread starter Thread starter ReneeD
  • Start date Start date
R

ReneeD

I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE NAME. I
would like to add another field to this same table that is called Initials
and I would like this field to generate the initials based on the other three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my table as
I would like to link this table to another database that would use the
initials.
 
It would be a mistake to store it in your table.

Create a query with that computation in it, and save the query. Use the
query wherever you would otherwise have used the table.
 
Hi Renee,

Access does not let you define a calculated column inside a table. If
you are using a back-end database with the ability to do before-insert and
before-update triggers you could do it. If not, you can do it in code on
your input form. Whenever any of the names are changed, change the initials
column. However, you may want to consider what you are desiring to do
carefully. What happens when the data is added and/or changed through some
other method? Will your initials column get created/updated correctly.
Also, what happens when you have two people with the same initials such as
John Henry Smith and Jane Harriet Samuels? Won't that cause a data integrity
problem, associating the child records with more than one parent? Why not
just relate the child records through the person ID? You do have a person ID
of some sort?

Clifford Bass
 
Hi Clifford,

What you have said is true for current released versions of Access. However,
Access 14, currently in beta, supports "data macros" at the table level.
These work similar to triggers, and allow one to store a calculated result
that is guaranteed to be updated whenever one of the field values change.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Clifford Bass said:
Hi Renee,

Access does not let you define a calculated column inside a table. If
you are using a back-end database with the ability to do before-insert and
before-update triggers you could do it. If not, you can do it in code on
your input form. Whenever any of the names are changed, change the initials
column. However, you may want to consider what you are desiring to do
carefully. What happens when the data is added and/or changed through some
other method? Will your initials column get created/updated correctly.
Also, what happens when you have two people with the same initials such as
John Henry Smith and Jane Harriet Samuels? Won't that cause a data integrity
problem, associating the child records with more than one parent? Why not
just relate the child records through the person ID? You do have a person ID
of some sort?

Clifford Bass

ReneeD said:
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE NAME. I
would like to add another field to this same table that is called Initials
and I would like this field to generate the initials based on the other three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my table as
I would like to link this table to another database that would use the
initials.
Click to expand...
Click to expand...
 
Ok, I've created it as a query, thank you.

Now is it possible to have another field in the query that is not on any
table that will show a billable rate? The rate I will have to enter manually
for each name.

Renee

Douglas J. Steele said:
It would be a mistake to store it in your table.

Create a query with that computation in it, and save the query. Use the
query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ReneeD said:
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE NAME. I
would like to add another field to this same table that is called Initials
and I would like this field to generate the initials based on the other
three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my table
as
I would like to link this table to another database that would use the
initials.
Click to expand...

.
Click to expand...
 
The query I created in my first database I cannot link to my other database
like I was doing with my table. I need it set up so that if any data is
changed in either database its updated in the other.

Douglas J. Steele said:
It would be a mistake to store it in your table.

Create a query with that computation in it, and save the query. Use the
query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ReneeD said:
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE NAME. I
would like to add another field to this same table that is called Initials
and I would like this field to generate the initials based on the other
three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my table
as
I would like to link this table to another database that would use the
initials.
Click to expand...

.
Click to expand...
 
Hi Tom,

Appreciate your letting me know. That certainly opens up some new
possibilities for Access!

Clifford Bass
 
It's not possible. You must have a field in a table in order to be able to
accept values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ReneeD said:
Ok, I've created it as a query, thank you.

Now is it possible to have another field in the query that is not on any
table that will show a billable rate? The rate I will have to enter
manually
for each name.

Renee

Douglas J. Steele said:
It would be a mistake to store it in your table.

Create a query with that computation in it, and save the query. Use the
query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ReneeD said:
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE
NAME. I
would like to add another field to this same table that is called
Initials
and I would like this field to generate the initials based on the other
three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my
table
as
I would like to link this table to another database that would use the
initials.
Click to expand...

.
Click to expand...
Click to expand...
 
iletide şunu yazdı said:
Hi Clifford,

What you have said is true for current released versions of Access.
However,
Access 14, currently in beta, supports "data macros" at the table level.
These work similar to triggers, and allow one to store a calculated result
that is guaranteed to be updated whenever one of the field values change.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Clifford Bass said:
Hi Renee,

Access does not let you define a calculated column inside a table.
If
you are using a back-end database with the ability to do before-insert
and
before-update triggers you could do it. If not, you can do it in code on
your input form. Whenever any of the names are changed, change the
initials
column. However, you may want to consider what you are desiring to do
carefully. What happens when the data is added and/or changed through
some
other method? Will your initials column get created/updated correctly.
Also, what happens when you have two people with the same initials such
as
John Henry Smith and Jane Harriet Samuels? Won't that cause a data
integrity
problem, associating the child records with more than one parent? Why
not
just relate the child records through the person ID? You do have a
person ID
of some sort?

Clifford Bass

ReneeD said:
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE
NAME. I
would like to add another field to this same table that is called
Initials
and I would like this field to generate the initials based on the other
three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my
table as
I would like to link this table to another database that would use the
initials.
Click to expand...
Click to expand...
Click to expand...
 
iletide şunu yazdı said:
Hi Clifford,

What you have said is true for current released versions of Access.
However,
Access 14, currently in beta, supports "data macros" at the table level.
These work similar to triggers, and allow one to store a calculated result
that is guaranteed to be updated whenever one of the field values change.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Clifford Bass said:
Hi Renee,

Access does not let you define a calculated column inside a table.
If
you are using a back-end database with the ability to do before-insert
and
before-update triggers you could do it. If not, you can do it in code on
your input form. Whenever any of the names are changed, change the
initials
column. However, you may want to consider what you are desiring to do
carefully. What happens when the data is added and/or changed through
some
other method? Will your initials column get created/updated correctly.
Also, what happens when you have two people with the same initials such
as
John Henry Smith and Jane Harriet Samuels? Won't that cause a data
integrity
problem, associating the child records with more than one parent? Why
not
just relate the child records through the person ID? You do have a
person ID
of some sort?

Clifford Bass

ReneeD said:
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE
NAME. I
would like to add another field to this same table that is called
Initials
and I would like this field to generate the initials based on the other
three
fields. Is this possible in my table?
I have tried this statement:

Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1)

in a query and it works fine but I would like the field to be in my
table as
I would like to link this table to another database that would use the
initials.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top