I have a database where I track the distribution of Memoranda. Now instead
of having to key everyone on the distribution over and over and over is there
a way to just key their initials and when I tab out of the field it populates
is it with their full name? Keep in mind that I am entering multiple names
in this one field. I am a self taught access newb so any advice would be
greatfully appreciated.
Here's a shocker:
Your memoranda table should not contain ANY NAMES AT ALL.
You're making a very common beginner's error, assuming that all of the
information needs to be together in one field, or one record, or even one
table. It doesn't! The way a relational database like Access works is to store
information about each kind of "Entity" - real-life person, thing or event -
in its own table, and then use Queries to link the tables together.
If each person gets many memos, and each memo goes to many people, you need
*three tables*:
Memoranda
MemoID
MemoText <Memo field, or link to an external Word document, or whatever>
Title
DateIssued
<more info as needed about the memo as a thing-in-itself>
Recipients
RecipientID <autonumber or EmployeeID unique primary key>
LastName
FirstName
<other biographical info, e.g. office number, address, email>
Distribution
MemoID <link to Memoranda, what's being distributed>
RecipientID <link to Recipients, who it's being distributed to>
<any other fields about this memo being sent to this person>
Rather than putting all the recipients in one field in the Memoranda table,
you would add a new record to Distribution for each recipient.
To do so, you could use a Form based on Memoranda with a subform based on
Distribution; on this subform you'ld have a combo box displaying all the
recipient's names in alphabetical order, so you can just select the name from
the list (by typing the first letter or two into the combo, it will jump to
the name). Better than initials, since you might have JR being Jim Roberts,
or Juan Rodriguez, or Janet Richardson - or all three!
John W. Vinson [MVP]
