Question about lookup tables

[marf]

How do I handle this...

1. I have a TblProjects that has information regarding a Project (#, name,
date, etc). One of the fields is ProjectManager and another one is
ProjectSponsor. Both of these people will come from the Employees
table...What's the table relationship supposed to be?

2. Related to the question above... How do I handle the 0.1% cases where the
ProjectSponsor is not an employee and can be any unknown name

Thanks

 

[Klatuu]

Question 1 is pretty easy. You can just join the employee table to the
project table in two places in a query. I have a similar situation where a
contract has two different types of contacts. In the contacts table, there
is a contact type, P for Project Manager and T for technician. I created two
queires on the contracts table each filtered by the type. Then in my record
source query, I join each of those queries to present the two types of
contacts.

Question 2 can be resolved by the same method, but it may be a big impact on
your application. If you had a field in the employee table that designnates
internal and external people, that would take care of your problem, but would
also mean you have to make changes to other parts of your app to handle the
external people.

Or, another way would be to have another table for external people and use a
Union query to present the entire list. The problem here is that Union
queries are not editable.