Managing Several Many-To-Many Relationships 1

[MattGreer]

I'm wondering if there isn't a better way to manage this.

I've got a database of Christian songs. About 400 of them or so. Each one can have several themes. Previously I've limited each song to two themes, but really there could be up to six or seven of them.

So I'm left with the (I think undesirable) option of taking my main table and adding more rows to it, calling them Theme1, Theme2, Theme3, Theme4, Theme5, ad nauseam.

What do you guys think? Should I just create one "memo" field and add all the themes in there, such that a song could have an entry like this:

Confession, Holy Spirit, Praise, Worship

Or should I just add a bunch of fields?

I appreciate the advice!

Thanks!!


Matt

 

[MattGreer]

It's a many-to-many relationship based on SongID. Pretty straightforward. Don't know why I didn't see it.

Mod, delete this thread if you want!

Thanks!!


Matt

 

[jedraw]

Song--->SongHasTheme<---Theme

For M:M break itinto two 1:Many

 

[MattGreer]

Yep! Now I just have to figure out how to build a form to start adding all these "themes" to each song. Not very straightforward.

(Yes, if you look closely, you see the mis-managed "Use1" and "Use2" which will be removed once I get the Themes all updated.)

Thanks!!


Matt

 

[dhookom]

Create a small, one column subform with a record source of tblThemeLink. The one field displayed is a combo box with a row source of

SELECT ThemeID, Theme FROM tblThemes ORDER BY Theme

The Control Source is ThemeID and only the Theme column is displayed. Set the Link Master/Child properties of the subform to SongID.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

I just can't get it to work Duane. I'm looking at the Northwind db and I'm missing something, probably a lot of things. I'll keep trying. This is fun, and I'm ignorant, but I'm stubborn, so I'll figure it out sooner or later. I appreciate your attempts me!

What I've got now is a form/subform but I can't add records to the subform. And that record doesn't get saved and stays at the same selection for each Song I parse through.

Thanks!!


Matt

 

[dhookom]

Is the subform continuous and allow adds and edits?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

Got it.

Thanks!!


Matt

 

[MattGreer]

It's hard to explain what I figured out, but it was an issue of the combo box control not having a Control Source (don't ask me why) and then I left out the ThemeID (unchecked) in the query. I sort of stumbled into it. Working now tho, thankfully! I appreciate your help!

Thanks!!


Matt