Table Field Auto Update?

[Guest]

My table has the following...
Last Name (Smith)
First Name (John)
Full Name (Smith, John)

(The short reason why is because different departments have different
needs/wants).

My question; with just entering Last and First, can I have Full populate
automatically? Either from the table design or using a query? Seems like a
simple thing; or so I'm hoping. Thanks! j

 

[Rick B]

You don't need Full Name. In your forms, queries, and reports, you can
build that by concatenating the two fields.

For more details, read the hundreds of previous posts on the topic of
redundant data in a table.

 

[Guest]

My newly created DB is being used to export data into an older existing
database with two different tables for two diferent departments. Currently,
one department enters First and Last in their table and the other department
enters Full.
A third department now has the responsibilty of entering the data (names
plus a bunch of other data) and we're exporting it to the older DB and
appending to the existing two table. Both existing tables have thousands of
records and neither wants to change their format. That way my DB has to be
able to export First, Last and Full.

 

[Rick B]

Export the data to your old databases from a query, not a form. Then just
build the concatenated field in a new column of your query, then export the
data from the query.

If it were me, I'd have the other departments normalize their data though.
It is silly to enter it in multiple formats. What if they want to print the
name as First, then Last for some name tags or a report some day? You can't
(shouldn't) store data in every conceivable format.

Also, I would put all of this in ONE database and then let the various
departments LINK to it, not export it. Again, it seems silly to be
distributing the data and hoping that you are using up-to-date information.

 

[Douglas J Steele]

Store only First and Last. Create a query that returns First and Last, plus
a computed field that concatenates the two. Use the query to export, not the
table.

 

[Rick B]

Note that I would have ONE table for both departments, not a separate table
for each. I would add a "Department" field to allow you to specify which
employees were in which departments.

Check out the various "normalization" articles and postings in the help
files and on the Internet.

 

[fredg]

My newly created DB is being used to export data into an older existing
database with two different tables for two diferent departments. Currently,
one department enters First and Last in their table and the other department
enters Full.
A third department now has the responsibilty of entering the data (names
plus a bunch of other data) and we're exporting it to the older DB and
appending to the existing two table. Both existing tables have thousands of
records and neither wants to change their format. That way my DB has to be
able to export First, Last and Full.

You seem intent upon using Access improperly.

Store just the First Name in it's own field, and the Last Name in it's
own field.

Then create a query that concatenates the first and last name

FullName:[LastName] & ", " & [FirstName]

add whatever other fields to the query that are needed.

Export the query, not the table.

 

[Guest]

My newly created DB is being used to export data into an older existing
database with two different tables for two diferent departments. Currently,
one department enters First and Last in their table and the other department
enters Full.
A third department now has the responsibilty of entering the data (names
plus a bunch of other data) and we're exporting it to the older DB and
appending to the existing two table. Both existing tables have thousands of
records and neither wants to change their format. That way my DB has to be
able to export First, Last and Full.

You seem intent upon using Access improperly.

Store just the First Name in it's own field, and the Last Name in it's
own field.

Then create a query that concatenates the first and last name

FullName:[LastName] & ", " & [FirstName]

add whatever other fields to the query that are needed.

Export the query, not the table.

Trust me, it's not me who's intent on using the db improperly. I'm just the
lowly newby - wannabe Access developer who's trying to do things the right
way which is why I've become so dependant on this wonderful message board.
It's the experienced developer(s) who set up their original db and said they
don't want to have to change anything in their existing database to
accomodate the exporting of my data which in the end is going to save them a
ton of time.

 

[chris.nebinger]

DJ,

I understand that sometimes business requirements trump good database
design & development methodology. However, we feel that you should not
have First, Last, and Full name fields in your database. What happens
when Sue Smith gets married and changes her name? (or divorced). Now
you have to update 2 fields, and what usually happens is over time you
get data disconnects.

Instead, have:
tblPeople
FirstName
LastName


Query_Export

Select FirstName, LastName, LastName & ", " & FirstName as FullName

From tblPeople

Use this query to export out the data you want. You could also have a
Middle Name field, a Suffix field (Jr., Sr., III, etc.), as well as
prefix field (Mr. Mrs., Ms., Dr., etc.)

Chris Nebinger

 

[Guest]

Thank you Chris and everyone for your help.

DJ,

I understand that sometimes business requirements trump good database
design & development methodology. However, we feel that you should not
have First, Last, and Full name fields in your database. What happens
when Sue Smith gets married and changes her name? (or divorced). Now
you have to update 2 fields, and what usually happens is over time you
get data disconnects.

Instead, have:
tblPeople
FirstName
LastName


Query_Export

Select FirstName, LastName, LastName & ", " & FirstName as FullName


Use this query to export out the data you want. You could also have a
Middle Name field, a Suffix field (Jr., Sr., III, etc.), as well as
prefix field (Mr. Mrs., Ms., Dr., etc.)

Chris Nebinger

 

[Jeff Boyce]

I'm pretty sure that you consider yourself "advised" at this point, that you
do not need to calculate and store FullName in order to be able to export
it, give FName and LName fields.

Your original question asked about a way to have Access auto-generate the
calculated field ... and it can't, at least, it can't at the table level.
What you're describing would be called a "trigger" in SQL-Server, which
would allow you to create a calculated field value (not that it would be any
better an idea there than in Access ...<g>).

Regards

Jeff Boyce
Microsoft Office/Access MVP