Relating by name is not a good idea here. Names are not unique and people change their names--both of which will cause problems for your related table.
Case ( IsEmpty ( Assistant Name ) ; "Faculty only" ;
Assistant Name ≠ database#2::Assistant Name ; "Faculty only" ;
database#2::Assistant type )
thanks--what would be a better way to do this matching?
By serial number defined in the assistants table.
These will always be unique and name changes won't effect the matching. Typically, we'd use a two column value list where this serial number is column 1 and the name (or a name plus additional data) serves as column 2 to link a record from Database#2 to Database#1.
So the assistant table in Database#2 would look like
Database #1 just has name--I'm not understanding how the ID number would be used for linking.
Thanks for your help!
Database #1 would also need a number field for the ID number. The name field would not exist in Database #1. Any time you need to show the assitant name, you'd add the related name field from Database #2 to your layout.
I'm not sure that would work in this case. The records in database#1 are coming in from an Excel file that's generated from a log of patient visits.
After you import from Excel, you'd need to run a script that finds and matches records--which puts us back to matching by names anyway. :-(
Sounds like you'll need to work out ways to handle duplicate names and also data entry errors that may simply mispell the names.