Create a new record from a form built on a related table

[izzo248]

My database consists of 2 tables: Stories and Themes.


Stories keeps track of various elements of literary stories (Title, Genre, number of pages, etc.). Key field is WorkID. Each story has one record. Sometimes more than one record can have the same title. I know which is which by the Genre field.

Themes is simply a table of themes that are yes/no fields. Any story can have any number themes. I tried relating this table via WorkID, which is a lookup field in the Themes table.

Each table has a form built from it.

On the Story form, there is a button called Themes.

If a record exists in the Theme with a corresponding WorkID, all is well: I can see the related record.

Problem is, I'm still in the data entry mode. Most of the records in the Story table don't have Theme records associated with them.

Is it possible to arrange things so that if a Story doesn't yet have a Theme record associated with it, clicking on the Themes button will create a new record for that story? Currently when I click on that button for such a story, a blank record is created, but the WorkID field isn't filled in. (It would also be great if I could have the Title and Genre appear).

Sorry if the wording of my question seems confused: I'm kind of new to this. And thanks in advance for any help that can be offered.
Izzo248

[June7]

I don't understand data structure. What is relationship of Stories and Themes - 1 to 1, 1 to many, many to many? If each story can have many themes and each theme can be associated with more than one story, then this is a many-to-many relationship and you need a third table (junction table) to associate stories and themes.

You might benefit from working through some tutorials to get a better understanding of relational database principles. Start with http://www.rogersaccesslibrary.com/

If you want to provide db for analysis, follow instructions at bottom of my post.

[izzo248]

I believe (apparantly naively) it's a one to one relationship. The "Themes" table (form) is simply a checklist Each field is a word followed by a yes/no checkbox. There are dozens of possible themes, and the list is probably going to grow. Random examples of themes: Love, Hate, Sex, Religion, Politics, Women, Men, Marriage, Jealousy, Immigration, Technology etc. If a story can be thought of as being thematically about Sex, Religion and Politics, I'll put a tick into those three fields and leave the rest unticked. This way, if I want a list of all the stories dealing with Religion and Politics, I can run a query asking for titles that have those words ticked and get my list. Not sure what a junction table is or what kind of data in my database such a table would contain, but I'll study your tutorials and see if I can find an answer. Thanks for what you did offer, and for your patience.

[June7]

Your Themes table is not normalized. That's often the case with multiple yes/no fields that all essentially represent the same kind of data. You can keep adding more 'theme fields' until you hit the 255 field limit, then what will you do? Maybe you never will, but every time you think of a new theme that means modifying table, queries, forms, reports, code.

You might also find that filtering queries on the yes/no fields can be frustrating.

A normalized structure would be a many-to-many relationship.

tblStories
StoryID_PK
StoryTitle

tblThemes - this would be a 'lookup' table of all themes that can be associated with a story
ThemeID_PK
ThemeName

tblStoryThemes - this would be the 'junction' table that associates stories with themes (genre) - each story/theme pairing would be a separate record
StoryID_FK
ThemeID_FK

[izzo248]

Thanks for explaining this so simply and clearly. Didn't know there was a 255 field limit (obviously, I'm a beginner). And yes, I was bothered by the idea of having to modify tables, queries and forms each time I add a theme.

What I'll need to puzzle out now is how make a query that asks for stories that have a specific combination of themes. (For example, Love and Politics and Religion), but that would be the subject of another post in another area of this forum. Thanks again for your generous help!

[June7]

First steps in solving that puzzle: review http://www.allenbrowne.com/ser-62.html

Actually, identifying Stories that have ALL of a given set of themes is trickier than with the multiple yes/no fields. Sorry, there's always a trade-off whichever design you go with. It is a balancing act between normalization and ease of data entry/output.

[izzo248]

I'll try one of each and see how it feels. I doubt that Categories will get anywhere near 255. Stories could run into the thousands.

[izzo248]

I made a form for entering data into the Themes table, all of whose fields are Yes/No fields. There are about 75 fields, and while I expect that number to grow, it won't be by that much. It works fine for data entry.

As you predicted, filtering is iffy. I made a query, and if I enter "True" into the Criteria cell of the Themes I'm interested, it works perfectly. But using the query is unwieldy. I'd much rather use the "Filter by Form" feature. Unfortunately, when I use that method, the results are not accurate. Some of the records that I ticked as "Yes" during data entry don't appear.

I notice that when I go into the "Filter by Form" screen, a lot of the checkboxes are grayed out. Could that be a clue about what I'm doing wrong?

Thanks in advance for any help,
Izzo248

[June7]

I've never set up a db with multiple yes/no fields for same type of data. I've never used filter by form either. So really can't say why the checkboxes are grayed out.

[Calego]

It sounds like the query you are building to create the form isn't designed correctly. Maybe you didn't link the Stories and Themes tables together. In design view, you see the boxes that represent Stories and Themes with the fields inside them, right? Well you actually need to drag with the mouse the Story-ID field in the Story table (box) to the Story-ID field in the Themes table (box). You'll see a line appear connecting them. Did you do that in your query design?