New DB with many to many relationships

[DannyBoy]

Hi,



It may well be that there is a simpler approach to what I am trying to do, but if there is, it has me stumped!

I work as part of a team who manage controlled release of information – effectively policing what information flows into, around and out of our group. Typically this is managed by use of a proper document management system, but as we don’t have one available and the requirement is for a limited duration, we have been making do with a combination of process, Excel and SharePoint. To date this has been going ok, however Excel is not really designed for what we are doing with it, plus it isn’t as robust as I would like, so I have taken it upon myself to try and establish something in Access.

In a nutshell, we have two key tables. One of these stores document data (which pulls repeating values from other tables, such as Authors, Document class codes etc) and the other stores the transmittal information (again this refers to other tables for repeating information, such as recipient names, company addresses etc).

Now comes the part I am having the trouble with… Many documents can appear on a single transmittal and a single document can appear on many transmittals. To try and handle this, I have set up a join (or junction) table, with a one to many link from the transmittals table to the join, and a one to many link from the document table to the join. What I can’t get my head around is how I am supposed to populate the relevant data?

My expectation is that the document information is populated purely in the documents table, for which I have created a form. Only once a document has been created can it be added to a transmittal, however I am unsure about how best to handle this. I’m assuming another form, with a sub form embedded to populate the join table? Trying to get my head around this is just making it hurt!

This is my first foray into setting anything like this up, so would REALLY appreciate if any experts out there could give me some pointers on best practice or how to accomplish what I am trying to do. I’ve attached an example copy of my DB to give you an idea of what it looks like.

Thanks

[June7]

I don't claim to be an expert but will offer a suggestion.

Don't put DocumentID (nor DocumentRevision, nor DocumentTitle) in tblXmittals, it should be in tblJoinXmitDoc.

tblJoinXmitDoc
TransmittalNumber (foreign key)
DocumentID (foreign key)

Do not duplicate document title in tblJoinXmitDoc. I am not sure about revision. If you have a record for each document revision in tblDocuments, then don't duplicate the field.

Also, for simple values like "Issued" or "Closed", I would save those text descriptors and not the ID in tblXmittals. Same for document type, I would save the ClassCode and not the ID in tblDocuments.