Multiple possible links to other table.

[Forum Joe]

This might have been answered elsewhere, but I couldn't find it. If so, feel free to link to elsewhere, I'm sure there's a common (if not simple) solution.

I have an employee table, with an ID number, a username and a full name.

I also have a Job table, which has a Employee ID as a linking field, so where Job::Employee ID = Employee::ID.

The employee can create a Job, and I want them to be able to enter their username OR their ID number into the dialog. If they enter ID number, it's easy. If they enter username, ideally I'd search the Employee table for that username, get the ID number, and paste that into Job::Employee ID.

However, I the employees don't have access to the Employee table to do the search. The tables are in seperate databases files, and I don't want to have to change all the permissions for the entire second file just to give employees the ability to search one field of one table.

Is there a workaround solution?

[Ender]

If they are creating jobs for themselves, why not have the database populate the EmployeeID automatically? You should be able to get that from a relationship between a global or unstored calc in the current table to the Employee table via the username.