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 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...

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.

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?

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.

I have downgraded it to 2003

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

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.

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.

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.

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.

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.

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.

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?

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.

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.

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

:D

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.

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.

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?

Did you search for more information about multi-select listbox? There's plenty of information on how to build one, including one that forbids duplicates.

Yes, Banana, I did. I understand them I believe. The question is do I really need them based upon the method of data entry I am using. Are they needed, given my explanation in the last post??

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.

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 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.

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.

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.

Nexshark, remember this:

There's always more than one way to skin a cat!

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...

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.

Here's a very very simple sample. This uses a combobox in a subform that will allow you to associate document with more than one deposition, and this uses the junction table correctly.

HTH.

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.

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??

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 (http://www.mvps.org/access/lookupfields.htm).

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...:(

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.