Easy Question about Many to Many

[hoodoo]

Hello!



Intro: I am a beginner in Access and have to make a table that I will explain later. I've read about this in 2 different books, but still cannot fully understand how can I achieve what I need, so I am hoping the forum might help.

Task: I have made a table (Books) containing list of books with related fields (BookID, Book Title, Author, Year Published etc.), but I have to make a field (column) that contains the genre of the book. However, one book might have one or many genres(I assume a book might have 1, 3 or even 20 genres), and each book may or may not have set one of the genres as primary genre. So, I don't really know how to show that in the Books table effectively.

I believe the solution to my problem has something to do with many to many relationship, I even made such relationship (I'll post a sceenshoot), but now what? How does it help? Also, my lecturer advised me to remember the rules of normalization when doing this.



Thanks!

[ranman256]

You only need the relationships for parent-child tables. Above, your BOOKS->Review quotes is nessessary to retain the integrity to the parent/child.
Genre 'catagory' tables are not vital.
(plus your example ,of using GENRE ID is not needed) The Genre itself: Horror, Drama, mystery would be the key. Making numeric keys out of this is overkill.
I would loose the GenreID and just use the text Genre field. (but yours is a sub-genre?)

The VITAL relations are parent-child ownership.

[JamesDeckert]

You won't show it in the table.
Build a form which shows the Books, with a subform which shows the Genre field and the Primary Genre field.

[hoodoo]

You only need the relationships for parent-child tables. Above, your BOOKS->Review quotes is nessessary to retain the integrity to the parent/child.
Genre 'catagory' tables are not vital.
(plus your example ,of using GENRE ID is not needed) The Genre itself: Horror, Drama, mystery would be the key. Making numeric keys out of this is overkill.
I would loose the GenreID and just use the text Genre field. (but yours is a sub-genre?)

The VITAL relations are parent-child ownership.

Thanks for your answer! However, I already use a subform option to show insight of review quotes (which is different task actually), thats why it shows up in the relationship window. I believe I cannot effectively use two subforms?

I added screenshoot of whats happening in the main table.

[hoodoo]

You won't show it in the table.
Build a form which shows the Books, with a subform which shows the Genre field and the Primary Genre field.

Thanks. But I have to use tables not forms.
Also, I believe, using subforms, would create a lot of repeating information about genres. There must be a more simple way how to do it. The exact task is "They are selling books. Books may be of different genres – e.g., classic, comedy, fantasy,drama, textbook, etc. A book can be attributed to more than one genre, but typically, there isone primary genre. " and I should do it according to normalization rules.

[ranman256]

sub-forms IS the way.

[hoodoo]

sub-forms IS the way.

Thanks bro, but this answer doesn't really help much. I understand subforms can be addend only to forms; whereas, I only have tables and I have to keep it so. How can I add something like subforms regarding that everything must remain in table format?

[JamesDeckert]

Humor us and build a Form (Create>Form Wizard) Select fields from all 3 tables (Books, Book Genres, Genres). It'll take 2 minutes. If you don't like the form then delete it.

[orange]

Hoodoo,
You are being given advice from experienced people. You are seeking advice, and they are providing it.
Why and where does this come from
"

whereas, I only have tables and I have to keep it so

".
Anyone with Access experience will tell you that no user works directly with tables where they can change and delete knowingly or unknowingly. Users interface to the database via forms --which can control/manage what a user can do with data.

Have you watched any tutorials re designing and building a database; MSAccess introduction to Objects; relational database design concepts?

[June7]

This is homework assignment? You are required to use only tables?

The design in posted image looks basically fine to me. Is PrimaryGenre a Yes/No type field? The Authors and Editors fields may be an issue. Normalization would call for related table to allow multiple authors/editors to be associated with book, each author/editor would be a record. Also, lookup table for authors and editors then save ID into the junction table. Suggest one lookup table because likely a person can be an author or an editor.

If you want to display multiple related dependent datasets, will have to use form/subform arrangement.

GenreID field should not be in Books.

I agree that using a GenreID may be overkill, so unless your instructor expects it, can be eliminated.

Advise no spaces in field and object naming convention as well as no special characters/punctuation - such as the apostrophe.

[hoodoo]

Hoodoo,
You are being given advice from experienced people. You are seeking advice, and they are providing it.
Why and where does this come from
"".
Anyone with Access experience will tell you that no user works directly with tables where they can change and delete knowingly or unknowingly. Users interface to the database via forms --which can control/manage what a user can do with data.

Have you watched any tutorials re designing and building a database; MSAccess introduction to Objects; relational database design concepts?

I agree with all of you that forms would be better, I have no doubts about your knowledge in Access, but I am specifically required not to create forms, but stick just to tables. It's an assignment. Also, we have had just two lectures about Access, so far, we have learned just about creating tables and making relationships, so it shouldn't be anything too advanced. I read through 2 books about these topics, but still couldn't fully understand the solution.

[hoodoo]

This is homework assignment? You are required to use only tables?

The design in posted image looks basically fine to me. Is PrimaryGenre a Yes/No type field? The Authors and Editors fields may be an issue. Normalization would call for related table to allow multiple authors/editors to be associated with book, each author/editor would be a record. Also, lookup table for authors and editors then save ID into the junction table. Suggest one lookup table because likely a person can be an author or an editor.

If you want to display multiple related dependent datasets, will have to use form/subform arrangement.

GenreID field should not be in Books.

I agree that using a GenreID may be overkill, so unless your instructor expects it, can be eliminated.

Advise no spaces in field and object naming convention as well as no special characters/punctuation - such as the apostrophe.

Thank you for your reply! Yes, exactly, this is an assignment and we are required to make tables; no forms.

It was a short text field, but I deleted it anyway. You are right, didn't even think about authors and editors. So should I just make a table of authors / editors and then somehow link in to the main table?

I just read about your suggested lookup fields, that really solves most of my questions! Thank you very much! I'll try to do this within a few hours, then I'll let you know if I need any more help. Thanks!

[June7]

Consider:

tblBooks
BookID

tblAuthorsEditors
ID
Lastname
Firstname

tblBooksAuthorsEditors
BookID
AuthorEditorID
Role (author or editor)

Why is author in ReviewQuotes? What are ReviewQuotes - critic reviews?

[hoodoo]

Consider:

tblBooks
BookID

tblAuthorsEditors
ID
Lastname
Firstname

tblBooksAuthorsEditors
BookID
AuthorEditorID
Role (author or editor)

Why is author in ReviewQuotes? What are ReviewQuotes - critic reviews?

But why do I have to create seperate tblBooksAuthorsEditors? Can't I just add field "Role" in the tblAuthorsEditors?
How I would do it, I would also make a table of all authors and editors, and just make fields "Author" and "Editor" in tblBooks as lookup fields.

The ReviewQuotes is really not revelant here, I have to include a couple of citations from book reviews for each book. And the "Author" there is the author of the review not the book, I understand it looks comfusing, so I'll change it.

[June7]

Because a book can have multiple authors and an author can have multiple books. This is a many-to-many relationship and requires a junction table.

Maybe tblAuthorsEditors should be tblPeople, then you could also have the quote authors in that table.