Subforms manytomany - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Subforms manytomany

Monica Jones

3 years ago

Im building a database for all my media (books, movies, music) and I started with books. I have a CreatorT table for all names associated with a work. I made a many-to-many junction with JobNameT. One author could also edit an anthology and of course can write multiple books. When I added this to my form, I made two subforms, one for writer and one for editor, which works ok. Im now looking at how to design my form for graphic novels. There are of course many more people that go into making one of those. So, Id like to simplify the look by having tabbed subforms like on the People table in the Association database (lesson 8). From what I know that must be a one-to-one relationship though. Am I wrong and/or is there another way of doing this or something similar?
TIA

	Monica Jones OP @Reply 3 years ago
Started playing with the BookF and remembered while it's okay at displaying the data, I can't use it for data entry. So I have to find a workaround for that too. Any suggestions would be appreciated.

Kevin Yip @Reply

3 years ago

The pictures below are the Access forms I use for my movie collection database. Regarding the creators, I have only one subform showing all the people in different departments: cast, director, composer, etc., as shown in the picture. Cast and crew data are pulled in from IMDb automatically (which involves web-scraping and some complicated coding) so I don't need to manually enter them -- which would be impossible to do since movies have long credits.

The type of forms you use is determined by what type of data you have. Do you only store only music titles, artist names, year, etc., or do you also store track names, track run times, info about album editions (LP, cassette, CD, etc.)?

	Kevin Yip @Reply 3 years ago

	Kevin Yip @Reply 3 years ago

	Monica Jones OP @Reply 3 years ago
I love what you did there! Mine is an entire rebuild of a previous database where I wasn't even using ID#s. You're pulling the info from IMDB, I'm wondering how to handle in input on mine. Here is a pic of the plan I drew up in Excel.

	Monica Jones OP @Reply 3 years ago

	Monica Jones OP @Reply 3 years ago
I should also mention that in addition to the CreatorXJobT junction table I also have CreatorXJobXMedia that joins the junction to the BookID. I think this is part of what is over complicating the process.

Kevin Yip @Reply

3 years ago

There are many online databases that you can pull info from, such as Goodreads or Worldcat for books, and AllMusic for music. But you probably need to do some web-scraping if you want to automate data entry. IDs are a must, because a database requires them. You can use ISBN or UPC for books, and UPC for LP, cassette, and CD. For movies, you may use IMDb IDs like I do. In this type of application, the gathering of the information is the toughest pa

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View