Relationships of Relationship 1

[Accesser]

I have an Access 2000 database for indexing and summarizing paper documents. There are 3 tables:

tblDocumentInfo
DocNumber (primary, autonum)
DocTitle
DocDate

tblCompanyInfo
DocNumber (integer, foreign)
CoName
CoAddress1
CoAddress2
etc.

tblOperationsInfo
DocNumber (integer, foreign)
OpType
OpDates
OpHistory

For every Document, there may be multiple companies listed, and multiple operations.

Here's the snag...I've come across a document that LISTS various OTHER documents and, of course, other companies and operations.

Problem: I don't actually have the other documents listed, and therefore, cannot assign them a DocNumber. It would be nice to enter these other documents, companies, and operations as a subset of the main document (which I do have), but I don't know how...

Any insight into setting up the relationships would be greatly appreciated. THANKS.

 

[LittleSmudge]

I think what you're after will involve adding one Foreign key field to tblDocumentInfo

tblDocumentInfo
DocNumber (primary, autonum)
DocTitle
DocDate
DocNumberRef


Then load the Master collection document that containes the list as a standard document and link to it any Company and Operation information that directly relates to the document.

Then for each 'subDocument' - load that in using the same procedure - but also store the Master Document's DocNumber into the 'subDocument's DocNumberRef field.

I know this structure looks odd when you first come across it but it is okay. It is often found in OrgChart /Staff reporting structures where a manager's Id is stored in the record of each person in their direct report tier. This means that you can cascade as many times as you link ( eg A Master Document can contain documents and it can contain other master documents as well - this structure will cope )

What the above structure will not cope with, though, is the possibility ( if it exists ) if a subDocument being listed in more than one Master Document.

If you need to cope with this eventuality then create a new table as follows

tblMasterSub
MasterDocRef
SubDocRef


Then load Master Document as usual,
Load sub documents one at a time and along with the Company and Operations info you should populate the tblMasterSub table with the Master Doc's DocNumber and the subDocument's DocNumber.

Again this structure will cope with multi level if needed and it will cope with multiple listings.


'ope-that-'elps.

G LS

 

[Accesser]

Thanks again LS