If you have a table of Full College Names with one field for the full name and one for the short, you can set up a relationship matching by short names in order to access the appropriate full name. But this will only work if the short names are 100% consistent within your imported data. If there are multiple possible short names for the same college, this will be more difficult to set up though it still might be possible to do.
Thanks for the help - I'll give it a try tomorrow.
OK - I'm feeling dumb. I have value lists and text fields for both long and short college names and they are 100% consistent, but what next?
Do I need a calculation for the "longcollege" field to recognise and convert the short "college" names ?
Thank you again
You need Two tables:
Fields: LongName ; ShortName ---- Both text. A __pkCollegeID auto-entered serial number field might also be useful
Fields: __pkStudentID ; StudentName ; CollegeShortName
Now establish a relationship:
Students::CollegeShortName = CollegeNames::ShortName
Now, on your Students layout, you can add the CollegeNames::LongName field to the layout and it will display the long name for the college if the short name in the Students::ShortName field matches to a record in CollegeNames.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thank you for taking the time to reply. I've done a lot of work on this database (I inherited the shell).
Hopefully this will be the last of my problems.