INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Managing Several Many-To-Many Relationships

Managing Several Many-To-Many Relationships

(OP)
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:

CODE --> MemoFieldTheme

Confession, Holy Spirit, Praise, Worship 

Or should I just add a bunch of fields?

I appreciate the advice!

Thanks!!


Matt

RE: Managing Several Many-To-Many Relationships

(OP)
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

RE: Managing Several Many-To-Many Relationships

Song--->SongHasTheme<---Theme

For M:M break itinto two 1:Many

RE: Managing Several Many-To-Many Relationships

(OP)
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

RE: Managing Several Many-To-Many Relationships

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

RE: Managing Several Many-To-Many Relationships

(OP)
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

RE: Managing Several Many-To-Many Relationships

Is the subform continuous and allow adds and edits?

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

RE: Managing Several Many-To-Many Relationships

(OP)
Got it.

Thanks!!


Matt

RE: Managing Several Many-To-Many Relationships

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close