Access Relationships??

[TEJ09242010]

I am brand new to using access databases but I am in the midst of trying to set one up for controlled distributions of controlled documents.



Essentially, I have three tables: 1) Listing of all our controlled documents and their properties (doc number, doc title, doc revision, doc type, doc status, effective date), 2) Listing of all our copyholders (name, location, phone number), 3) Document Distribution List which lists copy numbers, quantities, and medium to deliverable (paper, electronic, size, etc)

What I want to do is create relationships within the Document Distribution List (using 'add from existing fields') from the other two tables so when a request for a new controlled copy comes along, I don't have so much data redundancy.

For example, in Table 1, I have listed Document XYZ and copyholder Jane Doe from Table 2 wants to receive 1 copy of Document XYZ. I would like to have Table 3 have an added field from Table 1 where I can pull down the applicable document number, which when selected auto fills out it's corresponding details of title, revision, doct type, status and effective date in this Table 3. In addition, after I select the document Jane wants, I want to have another field added to Table 3 from Table 2 which lets me pull down Jane's name to assign her a copy number.

Does this make sense?

[June7]

Maybe. Third table would need foreign key fields to hold the primary key values from the other two tables.

tblDocDist
DocID (foreign key for tblDocs primary key)
CopyholderID (foreign key for tblCopyholders primary key)
other fields as needed (date of request, date sent, quantity, media)

Do not have fields in table 3 for document and copyholder details. Retrieve those details when needed by joining the tables on the key fields. This is a basic concept of relational database - do not duplicate data.