Hi Folks
I’m trying to create a database that (for the purposes of this question) contains two tables:
Table 1: People
Fields: employee number; last name; first name
Table 2: Documents
Fields: document number; document name; owner; administrator
The two tables are related in such a way that both the owner and the administrator fields in Table 2 are to be populated by data from the employee number field in Table 1. So far so good.
The problem I have is this. I want to run a query that shows for each document the employee numbers, the last names and the first names of its owner and its administrator. I assume I need my query to contain the following fields: document number (T2), document name (T2), owner (T2), last name (T1), first name (T1), administrator (T2), last name (T1), first name (T1). But how do I tell Access that the first last name and first name are to correspond to the owner’s employee number, while the second last name and first name are to correspond to the administrator’s employee number?
Apologies if this really is as basic as it sounds – this is the first time I’ve tried to create a database from scratch. The only option that’s occurred to me is to remove the owner and administrator fields from Table 2 and create two separate tables, one of which links an owner’s employee number, last name and first name to each document number, and the other of which links an administrator’s employee number, last name and first name to each document number. Then the query can expressly refer to each of these tables individually.
Thanks
Remster