Self-Referencing table question
- Thread starter nexshark
- Start date
Only you can decide that.
I've already also mentioned the possibility of using a subform that lists all related documents. I don't know of other solutions, but know that either multi-select listbox or subform in datasheet view or continous forms will do what you want to do.
After re-reading your post, I'm afraid that I'm a bit confused now and may need more clarification.
Earlier, you said you may have documents that may be related to more than one deposition. Now you're saying that documents can only be related to *one* deposition. Which is it?
Also, if your data entry person need to re-link document to same deposition (is that what you were trying to say?), then data entry form and possibly the design is flawed. Whenever you pull a old record, it should already have the information of what documents it is linked to. You also can create a query of documents that has no links to ensure everything is correctly linked. As I mentioned earlier, you can prevent duplicates using multi-select listbox or subforms for a many-many relationship, and using combobox for a one-many relationship.
Maybe you need to think carefully about what you are trying to accomplish here, because I'm not too sure what you really want.
I've already also mentioned the possibility of using a subform that lists all related documents. I don't know of other solutions, but know that either multi-select listbox or subform in datasheet view or continous forms will do what you want to do.
After re-reading your post, I'm afraid that I'm a bit confused now and may need more clarification.
Earlier, you said you may have documents that may be related to more than one deposition. Now you're saying that documents can only be related to *one* deposition. Which is it?
Also, if your data entry person need to re-link document to same deposition (is that what you were trying to say?), then data entry form and possibly the design is flawed. Whenever you pull a old record, it should already have the information of what documents it is linked to. You also can create a query of documents that has no links to ensure everything is correctly linked. As I mentioned earlier, you can prevent duplicates using multi-select listbox or subforms for a many-many relationship, and using combobox for a one-many relationship.
Maybe you need to think carefully about what you are trying to accomplish here, because I'm not too sure what you really want.
Banana, sometimes writing rather than talking has its drawbacks.
I am really ignorant when it comes to the multiple levels of thought involved in this, and for that I again apologize.
I think my error in reasoning lies in how I am defining "many to many" versus "one to many"....
If you will indulge me...
I give my secretary a stack of papers from several similar legal cases. There are depositions (these will always be Parent docs), and other documents given to the jury as evidence. She inputs each document of any kind, without regard for whether she has seen it before. Some of the documents given to the jury were also used as an exhibit in one or more depositions, but she really does not know that until she sees a document for the second, or third, or fourth time (it starts looking familiar).
So when she sees document A, she inputs its data. She then later sees it as an exhibit to a deposition, and she inputs its data and THIS TIME links it to a deposition. She sees the document later again in her work, attached to another deposition, and inputs its data again (all of this will be by autofill), and links it to this respective deposition.
So, each time she sees it she links it to only ONE deposition at most, and sometimes she will not link it to any other documents, except to a legal case or persons in the other subforms. I thought tthis was still a many-to-many relationship because when she is done doing all data entry, that one document will be linked in different instances of data entry to several depositions. Each deposition, likewise, may have had several different documents linked to them.
The question I think you have already answered is, this really is not a many-to-many relationship because each instance of the document only links to at most one Parent Document.
Am I understanding now??
I am really ignorant when it comes to the multiple levels of thought involved in this, and for that I again apologize.
I think my error in reasoning lies in how I am defining "many to many" versus "one to many"....
If you will indulge me...
I give my secretary a stack of papers from several similar legal cases. There are depositions (these will always be Parent docs), and other documents given to the jury as evidence. She inputs each document of any kind, without regard for whether she has seen it before. Some of the documents given to the jury were also used as an exhibit in one or more depositions, but she really does not know that until she sees a document for the second, or third, or fourth time (it starts looking familiar).
So when she sees document A, she inputs its data. She then later sees it as an exhibit to a deposition, and she inputs its data and THIS TIME links it to a deposition. She sees the document later again in her work, attached to another deposition, and inputs its data again (all of this will be by autofill), and links it to this respective deposition.
So, each time she sees it she links it to only ONE deposition at most, and sometimes she will not link it to any other documents, except to a legal case or persons in the other subforms. I thought tthis was still a many-to-many relationship because when she is done doing all data entry, that one document will be linked in different instances of data entry to several depositions. Each deposition, likewise, may have had several different documents linked to them.
The question I think you have already answered is, this really is not a many-to-many relationship because each instance of the document only links to at most one Parent Document.
Am I understanding now??
I understand completely. (RE garfunkling due to writing, not necessarily the concepts being discussed. 
)
Now it looks like we both understand this is one-many relationship, as we know that a deposition may have several documents linked to it.
But the million dollar question is: Can one document be linked to more than one deposition?
Is it possible *ever* for one document to be used twice in separate cases/depositions? If 'Yes', this is a many-many relationship. Otherwise, it's just a one-many relationship.
I think you know what follows after that.
)Now it looks like we both understand this is one-many relationship, as we know that a deposition may have several documents linked to it.
But the million dollar question is: Can one document be linked to more than one deposition?
Is it possible *ever* for one document to be used twice in separate cases/depositions? If 'Yes', this is a many-many relationship. Otherwise, it's just a one-many relationship.
I think you know what follows after that.
- Local time
- Yesterday, 18:44
- Joined
- Feb 28, 2001
- Messages
- 30,269
My original idea wasn't to have ONE combo box, but TWO combo boxes. One for "LeftID" and the other for "RightID" - which happen to be the same table reference and NOBODY (including Access) cares.
The rule is that a document MUST EXIST before it can be referenced with the combo box. Under "normal" quality control rules, you would enter all of your documents into the document table FIRST - and here you would have some chance to look for possible duplication of documents.
Then if the document isn't a duplicate, you would be allowed to build the links between two documents as long as the other document also exists.
My comments about the nature of the relationship presume that you might wish to link some depositions to a case transcript - or link the motions (filings) to the case.
In such a case, I would allow a place-holder to be created that contained an incomplete but usable case transcript entry - maybe pointing to a dummy file at first - so that you could track the relationship between an original complaint or tort filing, the motions at the beginning of the trial, the depositions provided at trial, and the decision after-the-fact. All of these would be different, each in its own way.
My thought was that you might wish to search for all secondary motions associated with the "Smith vs. Jones" case, or all of the depositions in the "Brown et al. vs. Murphy Oil" case. Or all documents that were final decisions of any case. This information CAN be captured in a JUNCTION table since only one code would refer to each of depositions, motions, decisions, appeals, etc.
But if you don't need that, what the heck, don't put it in.
The rule is that a document MUST EXIST before it can be referenced with the combo box. Under "normal" quality control rules, you would enter all of your documents into the document table FIRST - and here you would have some chance to look for possible duplication of documents.
Then if the document isn't a duplicate, you would be allowed to build the links between two documents as long as the other document also exists.
My comments about the nature of the relationship presume that you might wish to link some depositions to a case transcript - or link the motions (filings) to the case.
In such a case, I would allow a place-holder to be created that contained an incomplete but usable case transcript entry - maybe pointing to a dummy file at first - so that you could track the relationship between an original complaint or tort filing, the motions at the beginning of the trial, the depositions provided at trial, and the decision after-the-fact. All of these would be different, each in its own way.
My thought was that you might wish to search for all secondary motions associated with the "Smith vs. Jones" case, or all of the depositions in the "Brown et al. vs. Murphy Oil" case. Or all documents that were final decisions of any case. This information CAN be captured in a JUNCTION table since only one code would refer to each of depositions, motions, decisions, appeals, etc.
But if you don't need that, what the heck, don't put it in.
Doc,
By creating junction tables among all tables and the primary one, Documents, do I not retain the ability to query on ccase, or on people, etc... I thought I was maintaining that ability by creating the junction tables among those tables.
That said, your suggestion is that I need to create two combo boxes instead of a multi-select list box??
I understand and have created a junction table for Documents and Documents1. I just dont know how to go the next step if I now have to somehow link it to a set of combo boxes or multi-select listboxes... I must admit I am confused ass I am trying to understand what look like two very different approaches you and Banana are helping me with...but I am willing to learn...
If I create the junction table between Documents and Documents1 (the alias table). I then create two combo boxes in my data entry form...what do I input into them and what do they refer to? Do I need to use the SQL code that Banana helped me with or change the row source....? Sorry so many questions, just trying to digest the aggregate of the two plans.
By creating junction tables among all tables and the primary one, Documents, do I not retain the ability to query on ccase, or on people, etc... I thought I was maintaining that ability by creating the junction tables among those tables.
That said, your suggestion is that I need to create two combo boxes instead of a multi-select list box??
I understand and have created a junction table for Documents and Documents1. I just dont know how to go the next step if I now have to somehow link it to a set of combo boxes or multi-select listboxes... I must admit I am confused ass I am trying to understand what look like two very different approaches you and Banana are helping me with...but I am willing to learn...
If I create the junction table between Documents and Documents1 (the alias table). I then create two combo boxes in my data entry form...what do I input into them and what do they refer to? Do I need to use the SQL code that Banana helped me with or change the row source....? Sorry so many questions, just trying to digest the aggregate of the two plans.
Nexshark, remember this:
I don't think that my approach and The_Doc_Man's approach are different when looking about how to structure data.
We diverge on how you *present* the data to your users on the form. But that is purely a matter of style. Only *You* can decide what you want to see on the forms.
We both agree that yes, you need a junction table between Documents and Documents_1 if you do indeed have a many-many relationships between documents.
How you want to present this, be it a pair of combobox, a multi-select listbox, a datasheet subform, continous subform. In fact, we even could use textbox if we want (but that would be terribly unfair to your users, insisting that they commit to their memory the correct name of every documents).
It sounds like you decided that you need a many-many relationship and have set up the structure. Good. Now you need to decide how you want to present the data to your users.
Did that clear the matter?
Someone. But I'm going to bet it's missinglinq! said:
I don't think that my approach and The_Doc_Man's approach are different when looking about how to structure data.
We diverge on how you *present* the data to your users on the form. But that is purely a matter of style. Only *You* can decide what you want to see on the forms.
We both agree that yes, you need a junction table between Documents and Documents_1 if you do indeed have a many-many relationships between documents.
How you want to present this, be it a pair of combobox, a multi-select listbox, a datasheet subform, continous subform. In fact, we even could use textbox if we want (but that would be terribly unfair to your users, insisting that they commit to their memory the correct name of every documents).
It sounds like you decided that you need a many-many relationship and have set up the structure. Good. Now you need to decide how you want to present the data to your users.
Did that clear the matter?
Being that this is a data entry form only, I just need to make sure the realationships are correct for later queries, right?
When DocMan speaks of two combo boxes, are these the two that we created, being the original docTitle combo box(which for some reason now does not work...) and the new one you assisted me in creating? If not, what data woudl I be entering into the second oner if he means two NEW combo boxes somehow necessary to represent the many to many...
I am attaching the Db once again, as maybe you can tell me why the DocTitle Combo box no longer populates. I have not messed with this since we began work on the new combo box but it used to produce a dropdown list of DocTitles (like the new combo box now does)...
Banana, and docMan, again, I thank you for your patience. I feel like this data entry form is so close to completion, and this is the question that I have wrestled with for over a month now. If you can just help me to fight thru this...
When DocMan speaks of two combo boxes, are these the two that we created, being the original docTitle combo box(which for some reason now does not work...) and the new one you assisted me in creating? If not, what data woudl I be entering into the second oner if he means two NEW combo boxes somehow necessary to represent the many to many...
I am attaching the Db once again, as maybe you can tell me why the DocTitle Combo box no longer populates. I have not messed with this since we began work on the new combo box but it used to produce a dropdown list of DocTitles (like the new combo box now does)...
Banana, and docMan, again, I thank you for your patience. I feel like this data entry form is so close to completion, and this is the question that I have wrestled with for over a month now. If you can just help me to fight thru this...
I have tried to sort this out based upon prior input, have re-read all I could lay my hands on with reference to multi-select fields, junction tables (which I believe I have learned well in this project), and relationships which I know are of utomost importance. I am still stumped on the finer points of designing this data entry form to accomplish as indicated.
I created the junction table, have a combobox in the data entry form that references the second instance of the main table, to reference a parent documentwithin the same table. Doc Man indicates the need for two combo boxes. I am still perplexed on how to use this set-up. Again, the form is data entry, and I just need the data linked in such a way as to query later and have the documents related to other documents accounted for. As it stands, the junction table does NOT seem to be "plugged in" to the combo box created.
Can someone take a quick gander at this and advise???
Much obliged for your assistance.
I created the junction table, have a combobox in the data entry form that references the second instance of the main table, to reference a parent documentwithin the same table. Doc Man indicates the need for two combo boxes. I am still perplexed on how to use this set-up. Again, the form is data entry, and I just need the data linked in such a way as to query later and have the documents related to other documents accounted for. As it stands, the junction table does NOT seem to be "plugged in" to the combo box created.
Can someone take a quick gander at this and advise???
Much obliged for your assistance.
- Local time
- Yesterday, 18:44
- Joined
- Feb 28, 2001
- Messages
- 30,269
I suggested two combo boxes because you have two relationships to be managed.
Remember I talked about LEFT and RIGHT ID numbers? If you prefer to call these PARENT ID and CHILD ID number to get the concept clearer, fine. You would have TWO possible relationships for any one document. I suggested multiple combo boxes (one for each SIDE of that relationship) because you would never know until you actually were entering the relationship whether your point of reference was the parent (left side) or child (right side) of the junction. You could find the target document first, then have a left-side and a right-side drop-down for parent or child selection.
Alternatively you can have a checkbox to indicate parent or child. This would imply that the individual junction records are created by VBA rather than directly through a forms interface.
In either style, if you are looking a document that lists multiple sources (parents) you would add multiple parents for the child. If you are looking at a document that spawned many reports (children), you would add many children for that parent - but it's ALL FOR THE SAME RECORD that is the center of your focus.
My comments about a third field and third drop-down only are relevant if you have more than one reason that a relationship could exist AND YOU NEED TO TRACK THAT REASON. If not, skip it.
I sometimes take the "idea scattergun" approach and toss out stream-of-unconsciousness ramblings in the hope that something hits some part of the target. When I hit, I look like a genius. When I miss, I look like I've just blown chunks. But then, it's all free advice so it costs you nothing to ignore some of the chunks that don't seem to apply.
Remember I talked about LEFT and RIGHT ID numbers? If you prefer to call these PARENT ID and CHILD ID number to get the concept clearer, fine. You would have TWO possible relationships for any one document. I suggested multiple combo boxes (one for each SIDE of that relationship) because you would never know until you actually were entering the relationship whether your point of reference was the parent (left side) or child (right side) of the junction. You could find the target document first, then have a left-side and a right-side drop-down for parent or child selection.
Alternatively you can have a checkbox to indicate parent or child. This would imply that the individual junction records are created by VBA rather than directly through a forms interface.
In either style, if you are looking a document that lists multiple sources (parents) you would add multiple parents for the child. If you are looking at a document that spawned many reports (children), you would add many children for that parent - but it's ALL FOR THE SAME RECORD that is the center of your focus.
My comments about a third field and third drop-down only are relevant if you have more than one reason that a relationship could exist AND YOU NEED TO TRACK THAT REASON. If not, skip it.
I sometimes take the "idea scattergun" approach and toss out stream-of-unconsciousness ramblings in the hope that something hits some part of the target. When I hit, I look like a genius. When I miss, I look like I've just blown chunks. But then, it's all free advice so it costs you nothing to ignore some of the chunks that don't seem to apply.
Thank you both for hanging in there with me. Doc Man, I believe I fully understand the logic of the srtructures you discuss. And the "scattergun" approach you discuss is very useful, because I am one of those that needs to understand why, so I don't have to ask the same questions next time(!).
Banana,
I appreciate the example you provided. Again, so that I understand, you have the junction table with three unique ID's only one of which existed before...DocID. I added this as a subform, and cannot under any circumstances trigger the right field...Doc2ID...to add a record. I have tried adding a new parent Doc and attaching to existing child Doc. Likewise I have tried editing an existing child Doc to link back to a second Parent Doc. Never does a Doc2ID field in the junction table propagate...yet Doc2ID is called the Row Source for this Combo Box....
Is this working such that when I begin querying the relations will be noted??
Banana,
I appreciate the example you provided. Again, so that I understand, you have the junction table with three unique ID's only one of which existed before...DocID. I added this as a subform, and cannot under any circumstances trigger the right field...Doc2ID...to add a record. I have tried adding a new parent Doc and attaching to existing child Doc. Likewise I have tried editing an existing child Doc to link back to a second Parent Doc. Never does a Doc2ID field in the junction table propagate...yet Doc2ID is called the Row Source for this Combo Box....
Is this working such that when I begin querying the relations will be noted??
Going back to the example, I now know why it didn't work.
You used a LookUp Wizard in table view, which is *BAD*. Here's an article explaining why.
My example couldn't work because I expected to use DocTypeID (e.g. a number), when Access "sees" a text entry "Depo/Trial Document", so my query didn't work, and no "Type Mismatch" error was thrown (it should have, especially when we're trying to compare a number against a text, a logical impossiblity).
I'd strongly advise that you get rid of all lookups in your tables. Just blank them out, and hopefully everything will be back to normal and my query will work. You also will be relieving yourself of a big headache. No wonder you were so frustrated; you probably read in books about how keys are supposed to be used in relationship, and numbers are used in their place but you saw only the text.
I wished I had realized that earlier, but at least now we know the source of problem.
You used a LookUp Wizard in table view, which is *BAD*. Here's an article explaining why.
My example couldn't work because I expected to use DocTypeID (e.g. a number), when Access "sees" a text entry "Depo/Trial Document", so my query didn't work, and no "Type Mismatch" error was thrown (it should have, especially when we're trying to compare a number against a text, a logical impossiblity).
I'd strongly advise that you get rid of all lookups in your tables. Just blank them out, and hopefully everything will be back to normal and my query will work. You also will be relieving yourself of a big headache. No wonder you were so frustrated; you probably read in books about how keys are supposed to be used in relationship, and numbers are used in their place but you saw only the text.
I wished I had realized that earlier, but at least now we know the source of problem.
Banana,
I think I removed all hints of lookups within tables.
I did create combo boxes in the forms and subforms linked to the "lookup" tables...is that okay?
Having said that, I tried to add data and am still falling on my face here. The new query doesn't seem to find the related data (i.e. those items with DocType 1 or 6 as indicated inthe query.
I cleaned out the DB to load it here, as I believe is correct protocol...Can I impose upon you one more time to you take a look and see if there is some other stupidity I have incorporated here that is failing the query you created??
Again, my humble thanks for sharing your expertise...
I think I removed all hints of lookups within tables.
I did create combo boxes in the forms and subforms linked to the "lookup" tables...is that okay?
Having said that, I tried to add data and am still falling on my face here. The new query doesn't seem to find the related data (i.e. those items with DocType 1 or 6 as indicated inthe query.
I cleaned out the DB to load it here, as I believe is correct protocol...Can I impose upon you one more time to you take a look and see if there is some other stupidity I have incorporated here that is failing the query you created??
Again, my humble thanks for sharing your expertise...
Banana?? DocMan? Anyone??
Just wonder, per my earlier email, if I could have guidance on the self-referencing table set-up. I am too far along to turn back, but not quite at the point to make it all work... Any help would be appreciated.
Just wonder, per my earlier email, if I could have guidance on the self-referencing table set-up. I am too far along to turn back, but not quite at the point to make it all work... Any help would be appreciated.
Nexshark,
I tried to clean out your database, as you still have lookup fields, but it then crashed on me.
After second attempt, I got it to work fine. Take a look at it and see if you can understand what is going on.
I mention this because while your database seems to work OK, and you did the right thing by having the Name AutoCorrect off (I hope you did that before you created any new objects, right?), I would *strongly* recommend that you restart from the scratch.
Two reasons:
1) This assures that you did it right second time. It's a dreadful thing to go waltzing along only to find it go belly up few months ago because you missed out on one small glitch.
2) You've learned from the mistakes, and now know something, so you have an opportunity to make everything is named correctly, set up correctly, and so forth. It'll go much quicker the second time; I'm pretty sure this will be a couple of hours work for you.
Anyway-
One thing to keep in mind about linking multiple documents; combobox only let you choose *one*. If you need to link the document to yet another deposition, you need to advance to a new record on the subform where you link the deposition.
HTH.
I tried to clean out your database, as you still have lookup fields, but it then crashed on me.
After second attempt, I got it to work fine. Take a look at it and see if you can understand what is going on.
I mention this because while your database seems to work OK, and you did the right thing by having the Name AutoCorrect off (I hope you did that before you created any new objects, right?), I would *strongly* recommend that you restart from the scratch.
Two reasons:
1) This assures that you did it right second time. It's a dreadful thing to go waltzing along only to find it go belly up few months ago because you missed out on one small glitch.
2) You've learned from the mistakes, and now know something, so you have an opportunity to make everything is named correctly, set up correctly, and so forth. It'll go much quicker the second time; I'm pretty sure this will be a couple of hours work for you.
Anyway-
One thing to keep in mind about linking multiple documents; combobox only let you choose *one*. If you need to link the document to yet another deposition, you need to advance to a new record on the subform where you link the deposition.
HTH.
