Many relationships problem 1

[Accesser]

I'm wondering how to set up the following many-to-many relationships.

The 3 tables involved are as follows:

MtblDocumentData:
DocumentID (autonum, primary)
DocType (text)
DocDate (date)
DocSource (text)

MtblCompanyNames:
CompanyNameID (autonum, primary)
CompanyName (text)

MtblCompanyData:
CoDataID (autonum, primary)
CoAddress1 (text)
CoAddress2 (text)
CoCity (text)
CoState (text)
CoZip

The relationships are as follows:

For every Document in MtblDocumentData, there may be zero or more CompanyNames, and for every CompanyName, there may be 1 or more Documents. For every Document, there may be zero or more CompanyData, and a record of CompanyData may be provided in 1 or more Documents. For every CompanyName, there may be zero or more CompanyData, and a record of CompanyData may be provided for 1 or more Companies. The main thing is that all the data is pulled from Documents.

I'm curious what the join tables should look like, and how a main/sub/sub-sub form may be able to be linked.

Any advice/assistance would be greatly appreciated.

 

[LittleSmudge]

The line "The main thing is that all the data is pulled from Documents" is critical here.

What you are looking for is a classic Many-Many relationship between CompanyData and Documents

and another Many-Many relationship between CompanyName and Documents

Having drawn it out like that I am instantly surprised that there is no direct link between CompanyName and CompanyData - So I beg to ask the challenging question of whether you have this right to start with ?

I would be more comfortable if the CompanyName was the centre of the world. A company could have many addresses and an address could have more than one Company living there. The Document would then link off of the combination of the two.
In fact I'm so in favour of this structure I'll explain how this will work NOW & come back to the "Doument centred universe" in a minute.

Create a table called tblCoNameData like this

tblCoNameData
CoNameDataId Prime Key ( AutoNum )
CompanyNameRef - Foreign Key pointing to CompanyNameId in tblCompanyNames
CoDataRef - Foreign Key pointing to CoDataRef in tblCompanyData


Add a CoNameDataRef field to the tblDocumentData that is a ForeignKey to the CoNameDataId field in the linking table above.

Now for every document, create a record in the tblCoNameData. Populate the tblDocumentdata!CoNameDataRef with the prime Key from tblCoNameData and then populate the tblCoNameData foreign key fields with the primaryKey data for the relevant CompanyName and CompanyData.





Ok - so back to your structure
You need two Many-Many linking tables

tblDocumentCoName
DocumentRef
CompanyNameRef


tblDocumentCoData
DocumentRef
CompanyDataRef

Then just populate these tables with the links that join the pairs of talbes as you need them.



'ope-that-'elps.

G LS

(Come back with more questions, comments etc as necessary )

this table with all valid combinations of

 

[Accesser]

I was thinking about putting a join table between MtblDocumentData and MtblCompanyNames, and then another join table between MtblCompanyNames and MtblCompanyData. There is actually a direct link between the latter 2, MtblCompanyNames and MtblCompanyData. However, I've been wondering about the relationship between MtblCompanyData and MtblDocument Data.

I'm not sure I worded my relationships as well as possible. This dbase is basically a glorified index or abstract of documents we've collected. The problem is that these docs are messy with messy messy data. A Document:

--may just have an address, and we may not be able to decipher a company name;
--may have multiple company names without having corresponding addresses;
--may just have one company name with multiple corresponding addresses; and worst of all...
--may be a duplicate of another document, but with just a little something different, such as having a "certified" stamp or a handwritten note in the margin.

 

[Accesser]

I forgot another relationship--

If a document has multiple company names and lists multiple company addresses for those names, then the company addresses Must be linked to the particular company names and not just to the document in general. Messy.

 

[LittleSmudge]

Yes you're right - messy but not impossible

I appologise for getting my tags wrong in my earlier post. What should have appears was

Create a table called tblCoNameData like this

tblCoNameData
CoNameDataId Prime Key ( AutoNum )
CompanyNameRef - Foreign Key pointing to CompanyNameId in tblCompanyNames
CoDataRef - Foreign Key pointing to CoDataRef in tblCompanyData


Add a CoNameDataRef field to the tblDocumentData that is a ForeignKey to the CoNameDataId field in the linking table above.

Now for every document, create a record in the tblCoNameData. Populate the tblDocumentdata!CoNameDataRef with the prime Key from tblCoNameData and then populate the tblCoNameData foreign key fields with the primaryKey data for the relevant CompanyName and CompanyData.





Ok - so back to your structure
You need two Many-Many linking tables

tblDocumentCoName
DocumentRef
CompanyNameRef


tblDocumentCoData
DocumentRef
CompanyDataRef

Then just populate these tables with the links that join the pairs of talbes as you need them.



Given the additional data I think you do need the latter solution. In addition add a CompanyAddressRef foriegn Key field into the tblCompanyName

You can then directly link the company address to the name.


NOW you're going to have to be very careful developing the forms that allow users to load data into this schema reliably.


Talk to you soon.

G LS

 

[Accesser]

Thanks G LS