Self-Referencing table question

[nexshark]

Having (with much help from many of the experts here) overcome the hurdles of junction tables, subform to subform movements, and the seemingly easy design issues of a form, I have now created a self-referencing table. I humbly return to ask for advice from those experienced with these relationships...

I have been led to this in order to load in to my DB a certain class of documents, linking them relationally to their parent documents, while performing the data input for both into the same form. What I have done and what the problem is are as follows:

I created a new field in my form (formDoc), "LinkedExhibit". In the relationship window, I reloaded the Document table and reestablished all prior relationos, then added a second (alias) table Documents1, linking the primary key of this alias to the LinkedExhibits field.

I have the Linked Exh field set up as a combobox linked to DocTitle from the Documents table ONLY for the purpose of saving keystrokes if a duplicate should arise.

Unfortunately, when I enter a new document and then continue entering data through the LinkedExhibit field, and save by moving on to another subform related to these, the Documents table shows all of what I typed, except it shows the Linked Exhibit saved as DocTitle, and the DocTitle that I had typed is gone...

I really am flying blind but believe the alias table is not linked right, or I am using that concept to do something it cannot do...

I am attaching the DB, in Access2007, if someone could lend an eye to it...
I would appreciate that.

 

[The_Doc_Man]

The key to a self-referencing table is the same as any other potential many to many relationship - the JUNCTION table.

You have a junction table that is both left- and right- linked (so to speak) to the SAME TABLE. Then if you have Document 1531 is the parent of four documents, you have four entries in this self-link table that all point back to entry number 1531. And if two documents were used as the basis for writing 1531, you have two more entries that reference 1531 (from the OTHER side, since it is now child rather than parent).

If there are multiple possibilities as to why something is referencing other elements of the same table, you have to include a code in the junction table, such as "Derived from" or "Supplemental data" or "Transcript of case where this document was used" or whatever...

 

[nexshark]

So, Doc, you are saying I can create a junction table that links my Documents table to its alias table Documents1, with a unique ID in that junction table? I will play around with that. But, it is only going to be a one to many relationship between the one (Documents) and many (Documents1) tables... In that case, without a junction table, it should work, no?
Thank you so much for the input.

 

[nexshark]

Self join; self referencing query

I continue to fight my way thru this. I took a six hour course in "Advanced" Access Monday, to minimal avail. I have read over 2000 pages of books, searched this forum and others ad nauseum, and understand the theory, if not the logistics of how to achieve some of what Access can do for me. This forum has taught me normalization and table structure. This forum has taught me the use and format for junction tables. . I have searched, found, studied and will employ VBA code for autofill and autocomplete, among many other things the Forum has shown me.. I will use macros for easy stuff and convert to VBA code when in place. I understand their positive and negative side from Forum members perspectives..but I REALLY need to understand specifically how to use this self joining query...and it is driving me crazy

Doc Man and Banana, on different occasions, mentioned that a self-join query may be the solution to one of my problems. I read the Article by Allen Brown that banana sent in another of my threads. It was helpful in setting up the query. Doc Man's logic has helped immensely.

Here is the problem. I think I can set up a self join query, but in my Db I cannot get it to do what I know it is supposed to do.

First and foremost, I need to input information concerning a whole slew of documents. This is my primary table, having many-to-many relationships with the following: a people table, for people related to the documents; and a case table, for legal cases associated with the documents.

The purpose of the self-join is/was the following:
There are several document types being entered in the mainform. One of them is Deposition Testimony (transcripts), When legal depositions happen, witnesses can be shown one or many "exhibits". In the database these exhibits need to relate back to the Deposition in question.

Remembering that the form I speak of is a data entry form for the time being but may be used for reviewing and editing records as well, I don't know how to make the self-join query work on my data, and what it is that it will produce that can be queryable by the end users later...If I have to manually look up Doc ID's of the depositions to input into the self-relating field, I might as well duplicate the information...
Am I to use the query as the basis for the mainform? Am I linking the primary keys of the table (Documents) and alias table(Documents_1) correctly? To make the linked/joined field show a dropdown list of depositions to choose from, do I include the DocType as a query criteria before setting up the form, and if so, why can I not get it to execute this in the form?

Having struggled since the last week in May to create this DB from scratch, (and with much input from the experts here), I really could use some input on this... I am attaching a stripped down version of the form, the query, and the tables as they stand...

Can anyone save me from a meltdown at this juncture?

 

[Banana]

I'm sorry that it's frustrating, and I don't have 2007 (or unless you were able to downgrade to 2003 in that attachment?).

Without knowing more about your table structure and how you have written the query, it's hard to give specific answers. Maybe you could post the table structure and the query's SQL. (Just in case you don't know, when you open a query in design view, you can go View -> SQL View to see the sql commands.

That said:

What I envision on the form would be something like this:

I'd have all fields I need for a given document. At the bottom, I'd have a combobox. Its rowsource will query the same table to display all documents' name, perhaps with a criteria to limit only to deposition. No self-join is needed because it's just a rowsource; a list for us to choose and select from.

When a deposition is selected, its ID/Primary Key is then stored in the field of Document table... ParentID or something like that.

Now, if I need to see a document's dependency, that's when I actually use self-join query. I would have the query look up all documents having a <insert search criteria for document's name here> document's ID in ParentID field. I could also create another query to determine which are childless, orphans, or whatever.

Now, for the junction tables if we may have a many-many relationships. I think at this point, the question need to be asked:

Is it possible to have a document that is a child to another document which may be also a child to the first document? (It may sound absurd and therefore impossible, but I urge you to consider carefully exactly how are the document related, because if it's multi-facted and therefore pattern the general description, then it will be a tough nut to crack).

Assuming the answer is no, we basically treat junction table as if it's just one table; it inherits the primary key from the record (as a parent) and brings another primary key from the same table but a different record (as a child).

The only difference on the entry form would be that the combobox that looks up the document would be better replaced with a multi-select listbox. I *absolutely* love to use transfer list (or whatever they call that pair of listbox with <- and -> buttons in between where you transfer the record from one to other) for this kind of situation. The left listbox would list all documents meeting the criteria; it could be even same query in above example about combobox. Nothing interesting here.

But for right listbox, it will run a parameter query that retrieves all documents where its ParentID matches the current record. And you can add or take away documents which will then modify the underlying junction table. You do not need to refer to the junction table for your form's recordsource. (Yes, that means you need to use VBA to code for changes to junction tables.)

In either cases, you do not actually use self-join queries *until* you need to be able to see all related documents. It becomes more important in say, reporting. But for data entry, you only need a simple query displaying what documents are eligible to be parent and what document are indeed child of a given document.

I hope that helps.

 

[nexshark]

I have downgraded it to 2003

 

[Banana]

Oh, great. I'll take a peek at it when I get to work in an hour or so.

 

[Banana]

All right.

Now that I understand a little better about your data structure;

Let's start with a tangent.

Your self join query is pulling all fields from the Documents table. This can create performance hit when you don't really need all fields; you just need a ID and Document's name plus the criteria to narrow the doctypes to deposition.

Here's the modified SQL:

SELECT DISTINCT Documents.DocID, Documents_1.DocTitle
FROM Documents LEFT JOIN Documents AS Documents_1 ON Documents.RelatedDepoID = Documents_1.DocID
WHERE (((Documents_1.DocTypes)="1"));

That said, I am not seeing a combobox or listbox for you to select the parent documents from. It seems that we're using a textbox. Use either combobox or listbox as I described in my previous post then everything will work correctly.

HTH.

 

[nexshark]

Okay.. starting to come together

Banana, thank you for the help..
I added the SQL to the table in design view RelatedDepoID. I re-created the form with the new Documents table.
I added a deposition, then added an exhibit which I was to link to the depo. For sojme reason a Parameters box jumps up when I click on the combo box you wrote code for. I don't understand what it would want entered into the parameter box.

Next, you asked about there being a "triple decker possibility..ie a doc related to a parent doc which again is related to higher level doc. NO just the two levels you have accounted for.

Now, in your longer discussion, you started talking about juncton tables. I have them for peopl and for cases, as related to documents. Do I need to create a junction table to relate this new query in some fashion?

I have attached a cleaner version of the DB...can you just see if the code works like you intended?? Again it does not list in the combo box the prior deposition nor it's DOCID, and a parameter box jumps up..

Loads of thanks for your assistance, B.

 

[Banana]

Quick answers:

You only need a junction table if it's a many-many relationship. If all documents will ever have one parent document, then no you don't need a junction table.

I'll look at the DB later when I'm free. Parameter box probably because something differs in what I wrote and what you are using. Check if all fields name are there.

 

[nexshark]

a document can be linked to a deposition. Later I may find and enter data for that same document to be linked to another deposition. Depositions may have many such documents linked to them, and therefore there serems to be a many to many relationship.
Since we are creating the relationship through self-referencing combo box rather than a separate table, what am I creating the junction between...the documents table AND...? I cant imagine how to physically set that up, even tho I have set up junction tables among the other tables as illustrated in the example.

Again, my sincere thanks...I will wait to hear on the other issues.

 

[Banana]

Just because you will add more documents at a later time does not make it a many-many relationship.

Based on what I'm seeing, it does look like a one-many relationships, since all documents point to single deposition document.

If it is *ever* possible for a document to be related to more than one deposition, then yes, this is a many-many relationship. And that would also mean the combobox is wrong control for this. But we'll talk about it when you clarify the relationship.

As for the "triple-decker possiblity" you spoke of; I apologize if somehow I conveyed that idea in my earlier post as that's not what I was thinking about. But for your information, even if we wanted to have triple-decker, or even 10-decker, the structure as it is right now can handle this just fine. But hoo boy, is the queries going to be nightmare if you have 10 levels of paternity!

As for the combobox, I think I wasn't paying close attention; I recall me removing a table, which created a missing reference in the query and that's why you got the parameter box. Here's the correct SQL.

SELECT DISTINCT tblDocuments.DocID, tblDocuments.DocTitle FROM tblDocuments WHERE (((tblDocuments.DocType)=1));

I've tested it and it works fine.

Now, I want to call your attention to something. Notice that the SQL here only references one table. You already know that you want to display all depositions for this combobox. So that's all you need to do; put in DocType=1 (PK for depositions) as the criteria and select just the ID (to be actually stored in the table) and the title (to display to the users.

Combobox is set to show both columns. You don't need to show your users the ID column. In combobox's ColumnWidth property (under Format tab), enter "0;".

Then everything should work as expected. You'll see how the self join relationship are implied; there is no explicit relating between a table and an alias of itself in the relationship windows, neither do you use self-join query for combobox's rowsource. Self-join queries will be still needed when you need to make a report, though.

HTH.

 

[The_Doc_Man]

You only need a junction table if it's a many-many relationship.

Uhhh, no. If it is one/many, since it is self-referential, you need the junction table anyway. Particularly if there can ever be a parent, child, grandchild relationship, absolutely you will need an external item to list the individual relationships. If it were NOT self-referential, that would not be true.

 

[nexshark]

Doc Man...how??

Doc Man,
Banana has taken me a long way toward my end goal today...I really appreciate his assistance on this. Getting this relationship finalized is the final piece before I can begin loading documents...

Can you advise on how to create a junction table within the setup I have, for what I believe is a many to many relationship?
Specifically, each exhibit that is being linked back to a deposition, can appear in more than one deposition. Similarly, depositions often have more than one exhibit used in them...

Any instruction you can provide on what is now a fairly complex set of relationships for this novice?

 

[Banana]

Uhhh, no. If it is one/many, since it is self-referential, you need the junction table anyway.

I'm afraid I'm confused; you're telling me a junction table isn't needed, then reason that since it's self-referential, we need it anyway...Maybe you could clarify?

Particularly if there can ever be a parent, child, grandchild relationship, absolutely you will need an external item to list the individual relationships. If it were NOT self-referential, that would not be true.

I think I have a faint idea of what you're talking about. Could you be suggesting that whenever we may have several different relationship (in genealogical sense, not relational database sense) between two person, it is best to store in an external table, each possible relationship and the associated IDs?

Nexshark, you say that since one document could be related to more than one depositions while one deposition has several sub document... Sounds like a many-many relationship. At risk of giving you a bad advice (which would mean that you should really, really get a second opinion because I've never had that case myself), this is what I would have done:

Create a junction table:

DocID (Foreign key to Document table)
DepID (No relationship).

On the relationship window, you'd see only one-many relationship from Doctable to the junction table. Remember that we are talking about self-*join*, not self-*relationship*, as join and relationship are different.

On form, I'd just use a listbox with multi-select enabled. You still can use same rowsource as it'll list all possible depositions to relate to. Search the forum on 'multi-select listbox' on how to implement such one.

But I *repeat*, I've not had that case myself and I cannot guarantee you that this will be the correct way and would strongly recommend that you try and see if you can get second opinions. Maybe from this forums. Maybe from a Google search. (Have you looked at google groups? Useful infromation there).

HTH.

 

[The_Doc_Man]

you're telling me a junction table isn't needed, then reason that since it's self-referential, we need it anyway

(Banana asked...)

Of course I'll clarify. {shouts: OK, bring the mud truck over here...}

In a normal parent-child table, you have a field in the parent that is the ID of the parent. A foreign key in the child table corresponds to this key. You have as many child records as you need, each with their independent copies of the FK back to the parent. Whether the child has a separate PK is not required for this discussion.

But this is a self-referential situation - the same table is both parent and child. In order to make one record for every dependent entry, you need to duplicate the child record - but that means multiple pointers in the parent record (becomes a repeating group, violates 1NF). Or you need to change the table so that it cannot have a meaningful PK (just a random one) because the parent PK or the child PK will have to be replicated depending on the direction of the intra-record dependency.

The whole problem is solved if you have an external cross-reference table that lists the relationships. And technically, since one document can have many parent references or many child references - or both at the same time - it is a true JUNCTION table.

The junction table might be

tblIncest
LeftID - FK to document
RightID - FK to document
Nature - description of the reason this junction exists.
other date as appropriate (maybe the date when this relationship was entered if that is relevant?)

When you design such a thing, you build the main table and the junction table first, without a relationship. When you are done, then in the relationships window, you add the main table TWICE. This does not duplicate the table, it just duplicates a pointer to the table. If the first reference is DocTable, The second reference might DocTable(2). No biggie, it is really the same table.

Then you link LeftID to the PK of the document in DocTable and you link RightID to the PK of the document in DocTable(2) and fill in the nature of the linkage. If you were to print the JUNCTION table, you might see

Doc #12345 "requests a report, which is embodied in" Doc #54321
Doc #12345 "requests a report, which is embodied in" Doc #54322
...
Doc #22222 "is a response to motion described in" Doc #43210

And you could even design a table to translate the nature of the linkage to save space. So the junction table might be at most 3 integers - 2 x LONG and 1 x INTEGER or BYTE. Appropriate JOIN queries would allow you to expand those things on the fly to see what the codes and titles happen to be.

Not only that - you could build a form with three drop-down lists - one for each of the three integer fields I named - to populate them, since the document must exist before you can relate it to something else. That allows the drop-downs to look up the proper values. For each of the links from the junction table to the main table, it is a many (junction entry)/one (main entry) relationship. The part that makes it confusing is that depending on whether you are looking at the CHILD aspect of the record or the PARENT aspect of the record, you will have different numbers of references to the main table. And that is of course caused by it being self-referential as opposed to having separate parent-child tables.

 

[Banana]

Ahh, I had forgotten that multiple copies of a table on relationship window does not translate into *actual* copies. So my suggestion of having a half-completed junction table in my previous post was woefully wrong.

Nexshark, now you can see why this always will be true:

Where X=Great Minds

X+1>X

 

[nexshark]

So,
I create a junction table using tblDocuments and an alias of it as the tables being joined. I dont need, but Doc Man suggests, a third field "Nature" to describe relationships. I assume this could be an Autonumber Primary Key of this junction table, correct?

Am I still operating out of the combo box, or the listbox with the SQL you wrote, or how do I actually design this to work as it does now, but with proper relation of Many to Many?
THanks millions Banana and Doc Man.

 

[Banana]

It's up to you to describe if "Nature" is needed. Junction table usually have some extra fields that describe something about the pair that cannot be described alone. For example, if I have a junction table between table of People and table of employee positions, it'd make sense to store the effective date when a person started a new position in the junction table, as this cannot be completely described within person table (as it is about when this person started this new position) nor position table (since we need to know who started that position on that day).

Now, combobox is great for one-many relationship but this is now inappropriate for many-many because you can only select one value from combobox. So you will need to use multi-select listbox (there's plenty of topics on the forums about multi-select listbox) or if you want to avoid VBA, use a subform (a datasheet would probably be a good choice). Whatever you use, the query you used for that combobox is still valid. The difference is that you need to be able to select more than one possible document.

HTH.

 

[nexshark]

Banana and Doc Man,
I have a question. I have created a junction table in the manner described by DocMan.
In Banana's last post he talks of being able to select multiple documents in a multi-select listbox (I presume to link a child to many parents at the same time)

My data entry person will be entering 1000's of paper documents, and may or may not recognise duplicates as they occur. a deposition (always the Parent) will surface, and she will input information including perhaps exhibits (Child) to be linked ONLY to that deposition. 350 documents later, she may come upon a free-standing document identical to one of those earlier exhibits. She now will input this document (auto-filling fields upon recognition of its duplicate nature) with no link to any other document. Yet another 200 documents later, the same document shows up as an exhibit to another depositon. Now she links it to this particular deposition. During this data entry stage, I will never need to select more than one document with which to link this exhibit, for each instance the document surfaces to be input...Each time the exhibit is seen as part of a Deposition, it will be linked to one Parent document at most. But when I am done inputting 1000's of documents and depositions, some documents will have been linked to many Parent Documents, and some Parent documents will have many Child exhibits linked to each as well.

Because of the flow of data entry, I am confused as to whether during this stage the relations are true many to many.

See what I mean?
Can you help?