Data entry--freestanding documents vs. linked sub-documents

[nexshark]

Goodday.
I have a question that I wonder if you can assist me on...

I am new to creating access databases, and dont know any coding...

I have a data entry form. It centers upon document information being input, with authors, dates, etc. and linked to people and legal cases thru subforms. I have two groups of documents I will be entering. One is depositions and trial transcripts of witnesses from court cases; the other is a set of documents that may have been used in past trials, but that we will want to use in future cases.

The primary form is Documents, and relationally, all other tables are linked on a many-to-many relationship to Documents.

I am using a multivalue field for doctypes, which might include advertising mock-ups, video, correspondence, depositions, trial transcripts, etc. All of these are just stand-alone documents which will queried by person or by legal case name. Except for one doctype. The doctypes of the sort "deposition transcripts" may have exhibits attached I need to link back to that deposition. I had created a subform for deposition exhibits which I intended to fill only when the doctype is Deposition, and otherwise leave empty.

As I build this out, though, I realize that this would result in duplicate data in the exhibits and documents tables. (Some exhibits to earlier depositions are surely among the other stack of documents we intend to use in the future...

..If I could enter ALL documents and sub-documents in the same form/table, with some way of showing that a document is linked to (perhaps) the deposition I just entered, it will save a lot of effort.

Any thoughts that might lead me to an answer here?

As always, I appreciate any assistance you can provide.

 

[nexshark]

Is my best option to use the form and subform structure I have set up then query later to eliminate duplicates, or is there somthing I can code into this as a built-in link,,,

I can see using the documents form for all, adding a checkbox that asks "Is this an exhibit to deposition?' then confirmg which deposition (perhaps offering the most recend depo entered, as the default option to be confirmed. If not confirmed, full list opens of all depos. for clicking to establish the link.

I can dream, I guess.

AI donlt know if this requires a new table, form, query, or just some real slick coding...

 

[The_Doc_Man]

You should not worry about duplicates. If you have a document defined in your table and you need to use it more than once, you use the many-to-many (i.e. junction) tables to reference it more than once.

Never duplicate an entity you can just repeatedly point to.

 

[Banana]

Nexshark- I'm afraid I'm not quite understanding what the problem exactly is.

Doc man is quite correct; you will want to ask yourself- are you re-entering same document more than one time? If the answer is yes, then you have a design problem.

You may recall in another thread you asked about how to use many-many relationships, I explained that you had to bind the form to the junction table itself (preferably using a query, of course), then you simply had to select from a list of existing documents and tell Access what other document/whatever you want to associate with this document.

So, if you looked at the table view for document table, you should be seeing just one and only one entry for any unique document, whereas its ID may be duplicated several times in the junction table. Something like this:

Document Table:

DocumentID   DocumentName
1                  Bart Simpson v. Prinicpal Skinner
2                  The People v. George W. Bush
3                  John Doe v. John Doe
[color=1]4                  Bart Simpson v. Principal Skinner[/color]   'This record is duplicate and does not belong here

JunctionTable

DocumentID   ExhibitID
1                  1
1                  2
1                  3
2                  4
2                  5
2                  6
3                  1
3                  2
3                  7

See how DocumentID may repeat in junction table, and that's okay because they all point to just one record on Document table. Also notice how junction table allows for possibility of a exhibit used in two separate cases, so that's good, too. You just shouldn't be seeing two same records in a single table, especially if the only difference is a different primary key
Now, you mentioned something about deposition document being special; that it needs a subform which is not otherwise applicable for anything else, correct? If so, there's more than one way to skin a cat. I'll give you two to give you a idea of what you can do.

Make a unbound checkbox or a toggle button (mind you, not same as command button; those doesn't pop out when you click on it), which will reveal subform that was hidden by default and place the cursor in the first place to continue with the data entry. This is very simple to do, and if you need more real estate, you can use tab control and switch tab as well. Not to mention that it also fix the problem of have a big blank area when the subform is hidden.

Alternatively you can have a button that open a pop up form (you'll need to set the form's property Modal and PopUp to Yes so it will behave like a dialog and prevent user from going back to first form or whatever without completing everything in the form first. The problem is that it's no longer a subform which means you're now responsible for populating the ID field yourself using VBA code.

Now you talked about how several documents ultimately relate to one other, and so forth... I'm a bit concerned about how you have your tables set up. If you would mind, could you list your tables out here? Something like...

TableName
PrimaryKeyID
FieldName1
FieldName2
...
FieldNameN

This will help us ensure that everything is set up to not require unnecessary duplicate or extra work beyond the initial data entry and subsequent associations.

 

[doco]

Nexshark-
You may recall in another thread you asked about how to use many-many relationships,

Could you give a link to that thread?

Thanks

 

[nexshark]

banana,
Thanks for the input.
You brought up a good point, in that only the first subform seems to maintain links to the Form even when all are entered as subforms with proper relations...is this true, or only with Pop-up or Modal use? I am having problems setting up tabbed controls or, frankly any controls that keep the three subforms properly linked to the masterform.

My tables as they stand are:
DOCUMENTS
DocID (PK)
DocName
DocDate
DocDescription
DocTypes (multi-value field)
DocLink

PEOPLE
PeopleID (PK)
LastName
FirstName
Address
City
State
ZIP

CASES
CaseID (PK)
CasePlaintiffName
CaseState
CaseCourtSystem

EXHIBITS
ExhID (PK)
ExhDate
ExhTitle
ExhCategory
ExhDescription
ExhLink

JUNCTION TABLES as follows (per your earlier input)

PEOPLEDOCS
PeopleDOcID
PeopleID
DocID

CASEDOCS
CaseDocID
CaseID
DocID

EXHDOCS
ExhDocID
ExhID
DocID

Relationships are one to many to one from Docs thru Junctions to Exhibits, separately to Cases, and finally to Exhibits.

I will try to clarify...First, there will be thousands of documents input, selected from millions of available documents on the issues of our cases.

All the documents are freestanding..they need NO link to each other...except for in the case of the DocType "Depositions". When a deposittion occurs, the lawyer may hand any document to the witness for discussion. Hence, THOSE exhibits need to be linked to the deposition. Since the database serves two purposes, one to chronologise and allow link to imaged copies of depositions and their exhibits from a certain trial, and two, to gather a select set of documents for future trials, which may or may NOT have been used in the earlier trials, I am trying to avoid entering a document when I see it attached to a deposition, then later as I review other documents for inclusion would not remember it and re-input the same doc as one we want for future trials.

I am trying to input a macro that, upon entering a document's title as an exhibit, will go using DLookup to the docTable, and check for existence before allowing second entry. It seems as logical as any plan I have tried, and I intend tpo use that Macro in the Docs table as well anyway, so by recreating the same Macro for Exhibits field, maybe....

Sorry for rambling, and thank you again for any insight.

 

[Banana]

That's a great amount of information.

The reason why I was concerned earlier was because I was under the impression that deposition documents would have "accompanying documents"; did that mean you will be using some records from document to relate with despoitions, or are they stored as exhibits (and if so, are they still a document in same sense of the documents in doctable?).

Now, you say....

You brought up a good point, in that only the first subform seems to maintain links to the Form even when all are entered as subforms with proper relations...is this true, or only with Pop-up or Modal use? I am having problems setting up tabbed controls or, frankly any controls that keep the three subforms properly linked to the masterform.

I want to make sure we're talking about same thing; are your subform a freestanding form that is opened with a button or are they in a recessed boxes within your main form.

If you were opening them with button or whatever, then you are responsible for linking the record between the main form and pop up form (and that's where my paragraph RE: popup and modal applies).

Otherwise, subform control does that automatically for you, even within a tab control. To Access's eye, a form with X number of tabs is same thing as a really really big form. Therefore, you could have a main form using a particular recordsource on the first tab, and place subform on the second tab, and Access would be able to link the related fields between first form on first tab and second tab.

A unwanted comment: Avoid macros like the devil! I tried macros one time and that one time was enough. You're better off doing it hard way with VBA. Sorry, getting off the soapbox here....

That said, you don't even need to write a macro or VBA if you do this way:

First, make your form bound to the docTable. The only thing you need there is a combobox. You can use Wizard to make sure it is set up to show document title. Set the form's property so Additions, Edit, Deletions are set to false (under property window "other" tab) to prevent tampering with the search field (and subsequently the underlying record).

Drag'n'drop a subform to your new form, bind it to DocTable once again, link the subform to form using DocID, and there you can now add new records or edit whatever you need. To see everything that goes with the doctable, you then add another subform within the subform for the exhibits or people or whatever it is you want to see.

Hope that helps.

 

[nexshark]

I am sorry for digressing onto the tabs issue. I am addressing that in another thread.

That said, you don't even need to write a macro or VBA if you do this way:

First, make your form bound to the docTable. The only thing you need there is a combobox. You can use Wizard to make sure it is set up to show document title. Set the form's property so Additions, Edit, Deletions are set to false (under property window "other" tab) to prevent tampering with the search field (and subsequently the underlying record).

Drag'n'drop a subform to your new form, bind it to DocTable once again, link the subform to form using DocID, and there you can now add new records or edit whatever you need. To see everything that goes with the doctable, you then add another subform within the subform for the exhibits or people or whatever it is you want to see.

This is the part I need to better understand.
I set up a form(formDocs), and then three subforms(subformCases; subformPeople; and subformExhibits) created from queries that link thru junction tables back to formDocs. One of those subforms (Exhibits) was created only to be used when a certain document type (deposition) has attached sub-documents. After doing data entry of all depositions and their exhibits, I will be doing data entry of thousands of other miscellaneous documents, which may or may not be duplicates of the sub-documents I entered as exhibits to depositions. The question is how do I avoid the creation of duplicates. i am again attaching the DB as a zip...
It is in Access 2007 if anyone can help me with this.

 

[nexshark]

Anyone?

Need someone with Access 2007 I guess, to be able to review the zip...
Thanks for ANY assistance.....

 

[Banana]

All right. Now that I've seen your forms and table and have a better understanding- Let's do this one at a time.

When you talk about "sub-document" how do you have them stored? How are they related to other document?

 

[nexshark]

formDocs is the main form.
subformExhibits is a subform linked with junction table back to Docs.
I enter a doc, and later in the data entry form arrive at the exhibits subform, where, if there are sub-documents, i have similar fields (date, title, notes) that I fill in for the sub-document.
...I am starting to think that cascading combo boxes may work, based upon my reading...click depositions in the multivalue field, then a combo box opens asking if this is the depo or an exhibit to the depo... All other information is entered on the formDocs and the cascading combo box assigns one or many exhibits as linked to the given document...as I think this thru, though, I wonder how to ensure the exhibits refer back to the primary document... It is kind of like a loop in the data entry form...

 

[Banana]

Does that mean all related documents will always point to one desposition document?

This sounds more and more like self-join is what you need. Here's a nice article describing how this works.

If it is possible for sub-documents to be related to more than one deposition, then you'll need to use a junction table that looks up the document table. Cavaet: I don't know if a many-many self-join is possible or even a good idea but only because I've yet to hear of such concept. I'd want second opinions on this myself.

Ultimately, you need to decide if self-join is approritate for your data set as you know more about it than I.

Couple more questions- Whenever you have a new case or whatever, is/should Deposition be the first document to be entered? Is it possible that you may have other documents to enter before you actually have a Deposition and need to subsequently link documents to the Deposition? Does everything always depend on Deposition for query, lookup, whatever?

Sorry to ask so much questions; I want to make sure I'm not giving out wrong advices because of faulty understanding. I will comment on cascading comboboxes when you've answered the above questions.

 

[The_Doc_Man]

Regarding macros...

Don't avoid them when they are appropriate. Here is when they are appropriate.

1. You have a sequence of action queries (maketable or append followed by updates or deletes)

2. Build a macro to test the sequence for effect.

3. When you are satisfied that you have the sequence right, there is an option to convert a macro to VBA. So... convert! Using the macro as the starting point is often mechanically easier than keying in the raw VBA, particularly if you had a big bunch of complex macro actions.

4. The reason you eventually want to convert the macros is that they have limited to no error checking. Once you convert the macros to VBA, you can go back in and customize the error responses.

This is why I'm not so down on macros as some people seem to be. If you remember that they can be converted later, they make a great starting point for sequences of actions that you want to develop quickly. A macro is a very important part of the Rapid Applications Development (RAD) environment that is inherent in Access. Just like a wizard, the macro is as dumb as a box of rocks - but both can be used to quickly generate code for you. And that is a good thing.

 

[Banana]

While I don't know if Access 2007 made macros any better, I have long ago decided to avoid macros like plague after I made one macro and built it wrong. The trouble was it would not go away. Deleting it did not make the error message go away. I tried several things to make it go away but ended up making a new database and imported all objects BUT macros (even if there weren't any) to get it back in working order. Therefore macros were blacklisted in my book.

But to each his own.

 

[nexshark]

Banana and DocMan...many thanks for the insight.
In the course of learning to build this database over the past month, I have written some Macros and have experimented with converting them as well. I will use with caution, given Banana's comments, and my experience which is they don't want to disappear when you delete them(!).

Here is a scenario (using an old and well-known case as the example) detailing the documents and sub-documents and their interrelation, which will answer Banana's questions.

Suppose a client comes into law firm whose husband just died when their Ford Pinto's gas tank exploded.
I would want to assemble a set of documents that comprise all we know about the car and its competitors, reports of fuel tank explosions, and internal Ford correspondence or studies showing Ford knew about the problem for years. Now I go take a deposition (sit down and ask questions) of a Ford executive, and I show the guy a memo he wrote that says the cost of paying settlements would be less than the cost of recalling and fixing all the Ford Pintos, so he advises his managers to just ignore the problem.

That memo is already in the database as a document. Now, when i receive the transcript of the deposition and I load that document, as a "Deposition" document type, i want to reference which exhibits I was asking the deponent about... I either have to go fill in the linked subform, or find a way to internally link them within the documents table.

In a second scenario, I could also receive a deposition from a prior Ford Pinto case, that involves a witness I have not heard about, and which included documents used as exhibits that I had never seen, and therefore did not have in my database. I have to load that deposition and its exhibits.

Seems to me I could input documents and separately the ones used as exhibits, then later do a find and erase duplicates query, and another query to assemble all of them, or find a way to link the two tables into one, with some manner of self-referencing...

Sorry so longwinded...any thoughts?

 

[Banana]

Great. That was very helpful.

This is what I would do in general sense. If you think this is what you want to, we can hammer away on the specifics:

If I'm understanding you right, it may be possible to have a document without a case (e.g. your client may be considering a legal action but has yet to initiate any action, correct?). If this is the case, we will need a table to keep unique ID. Maybe make a Client table or something like to store information about your client and what they want to do. If case number is always assigned whether it was filed or not, then this will act as good key. The documents then can be linked toward this key.

So once I've enter my client or case number, I then proceed to create new documents in a subform linked to that client or case number. This will then tell me that those set of documents are related to this particular case. I do not need to worry about duplicates here.

I would then want to have a search combobox for client/case to retrieve the record in future when I have new document I want to add to the database.

But if it's possible to have existing documents referenced in more than one case, I would want to have another form using .... what was that called.... two listboxes where you select entries from left and it pops up on right... Damn, I forgot what that was called (not cascading because they're independent; left shows all listing whereas right one shows just stuff related to a given record). Anyway I use that a lot in my database. Maybe the form will have a combobox functioning as a search criteria to help narrow the listing on the left listbox which would have the tblDocument as the rowsource, while there's another combobox on right that let me select the appropriate client or case, which then query the related document and is used as rowsource for the right listbox. So you get to see all listing of documents already referenced in the given case and add existing documents to a new case that was bought at a later time.

The listboxes can be programmed to reject any duplicates, while you now have a mean to unite all documents to single case/client as the action progresses.

Notice in the above, there is no need to delete duplicates as the setup already will prevent any duplicate and make use of any existing record if you need to. This adhere to what database should be: Enter it *once* and *one time only*, and reference it a zillion times if needed.

Did that help?

 

[nexshark]

Banana,
That all makes sense. Thank you for the thoughts.
There does not seem to be a solution to the problem of needing to link certain sub-documents to their "parent document", in the case of depositions.
All else from the structure you outlined, it would seem, is accounted for by the current structure, and duplicates would only arise in the creation of the sub-document type Exhibits to Depositions, which I do not see a solution to in your structure...am I right?

 

[Banana]

Ah, I got wrapped in thinking about relationships between documents with the case, but neglected the relationships between a group of documents to document.

If you really want to be able to relate a group of document to depositions, then the above solution will work just fine- just substitute the Client/Case key with Deposition's key (in that case you want to use Self-Join I linked earlier allowing the document to be related to another document which is a deposition).

Did that answer the question?

 

[nexshark]

Yes. I am gonna play around with the self-join for a bit.
Thanks for all your guidance on this, Banana...
I hope to be able to report soon that my first DB is up and running!