Forms with sub-sub-forms

[Didcut]

I've read through several tutorials and read through documentation but it's still not clicking for me. I feel like what I'm trying to do should be relatively simple I just can't figure out how to do it.

I'm trying to create a database for book titles and because people can sometimes be authors or editors I want a separate table for People, one for people who are authors and the titles they have authored. I think this means I need three tables to hold the relevant data. My problem has been creating a form that allows me to actually input this data. I ultimately want to expand this to cover things like Editor and Illustrator but I felt like this was a good starting point.

So I've got a "Person Table" with FirstName Last Name PersonID
A "Author Person Table" AuthorID and PersonID
and a "Title Author Table" TitleID and AuthorID

Sometimes books have multiple authors and I believe this will account for that.

I've set up all the relationships but when I try to make a form to create a new Book Title it doesn't seem to connect at all with the other two tables.

[UnklDonald418]

Welcome to the forum.

The form at the top level (probably MainForm) will use your Book Title table as a data source.
The SubForm will use the intersection table named "Title Author Table" as it's data source. The SubForm will be linked to the Master Form using TitleID.
To accommodate multiple authors the SubForm should have a table control with columns for both TitleID and AuthorID. If the link is properly defined, the TitleID column will automatically have a value inserted, so you could hide that column (don't delete it, just hide it).
The AuthorID column should have a ListBox Control. The query for the listbox control would use the "Person Table" as it's data source.
You enter information for a title on the top form then on the listbox on the SubForm you select Authors(s).

There is no need for the "Author Person Table"

If you need further assistance please upload a sample of your database
How to attach a document here

[Didcut]

OMG thank you that worked like a charm and I feel like I'm finally starting to understand how this works. My only question now is how do I display the title of the book and the name of the author in the listbox/field form so I know what I'm selecting? Here's a copy of my little database.

Thank you for your help.

[UnklDonald418]

I made some modifications to your form document and saved it as Title Table01

People Database01.odb

(24.15 KiB) Downloaded 256 times

Let me know if you have questions.

[F3K Total]

One additional hint:
TitleAuthorID is not necessary. If you replace in table "Title Author Table" current primary key "TitleAuthorID" by using both "TitleID" and "AuthorID" as a combined primary key, see attached picture, you prevent the table from having an author-title combination twice.

PD.png (23.55 KiB) Viewed 3527 times

R

[Didcut]

I tried to expand my form so I could get other inputs like "editor" but when i added the table control for the editor table now I can't input a new person in the person table. Is this possible or do i need a whole separate form for inputting each new person so i can assign them as an "author" or "editor"?

I was hoping to be able to input the title information, author, editor, publisher, illustrator, fiction/non-fiction, a source URL of a copy of the book, and some topic information like "historical" "wood craft" "children's book" etc. Just so you understand where I am trying to get with this.

thanks for your help thus far.

[Villeroy]

I downloaded Uncle Dons example, applied the changes suggested by F3K Total, deleted one form, tweaked the other one a little bit and added one with reversed roles.
The 3 tables represent a many-to-many relation between Authors and Titles. Each author can have zero, one or many titles and vice versa, so every book can have zero, one or many authors. This is called a "many-to-many" (m-n) relation.
You have a list of authors, a list of titles and a table which maps unique combibations of authors and books.
A form reflecting a many-to-many relation has one list represented by a main form and the mapping table is always the subform.
The subform is linked through the common item ID shared with the parent form.
The subform has a table control in order to show many related items at once.
In this case the subform has only one column which represents the IDs of the related items.
These related IDs are represented by a listbox showing the related item's name in front of the hidden ID, so that you actually enter an ID by selecting a name. This is what list boxes do.
The sub-subform is linked to the subforms item through the common IDs. It simply shows the additional details of the selected subform item that are stored in the other list. The sub-subform is linked to its parent by the common ID.

List A --> Mapping Table --> List B
[A.ID] --> [AID ; BID] --> [BID]
Details --> linked items --> Details
Main Form --> Subform --> Sub-Subform
Autors --> Authors&Titles --> Titles
Titles --> Authors&Titles --> Authors

[UnklDonald418]

In answer to your question about adding Editors
Building on the version Villeroy uploaded, I added two Boolean fields (Author, Editor) to the Person table and set the default value for both to No.
They allow you to choose if a person is an Author or an Editor or both.
That allows the listbox queries to show only Authors or only Editors.
Since there are slightly different queries for each listbox, the queries are now stored in the listbox controls. To see those you need to look at the Properties List box dialog for each listbox
I added a Title-Editor intersection table and a form document Titles - Authors - Editors
That form document includes a separate Form for Person entry/edit.