Results 1 to 12 of 12
  1. #1
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243

    Creating a database in which one book will have several authors


    The conventional wisdom in Access is that one book title = one author -- and that's true for the majority of the books I'll be adding to this database now being designed. However, in this database I'm creating one book will frequently have several authors due to the collaborative nature of the university. How is a one title = many authors handled in Access?

    Thank you.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Have you read about junction tables?

    One book can have many authors and one author can write many books. This is a many-to-many relationship.


    tblBookTitles
    --------------
    BookTitle_PK (Autonumber)
    BookTitle (Text)


    tblAuthors
    --------------
    Author_PK (Autonumber)
    AuthorFirstName (Text)
    AuthorLastName (Text)


    tblBookAuthors (junction table)
    ----------------
    BookAuthor_PK (Autonumber)
    BookTitle_FK (Link to table tblBookTitles)
    Author_FK (Link to table tblAuthors)

  3. #3
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    I had created a junction table before I posted. I guess my question should have been if one book has many authors, then does each subsequent author on that book have to have his/her own table? That's the only way I can think of to give the second, third, fourth author, etc, it's own unique identifier that will link to a book with many authors.
    Click image for larger version. 

Name:	Rltnshps.JPG 
Views:	18 
Size:	28.5 KB 
ID:	24383

  4. #4
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243

    Update

    I figured out to create a many-to-many relationship. Now -- how do I address the Subdatasheet that appears?
    Click image for larger version. 

Name:	ManyToMany.JPG 
Views:	17 
Size:	100.4 KB 
ID:	24389

    Click image for larger version. 

Name:	ManyToMany2.JPG 
Views:	18 
Size:	67.7 KB 
ID:	24390
    Thank you.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I figured out to create a many-to-many relationship. Now -- how do I address the Subdatasheet that appears?
    I usually turn off the subdatasheets. All adds/edits/deletes should be done through forms.



    This is how I would create the tables/relationships:
    Click image for larger version. 

Name:	Books.jpg 
Views:	18 
Size:	22.9 KB 
ID:	24393

  6. #6
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Thought I had it resolved but, sorry, obviously the query check says otherwise. Would be grateful for help in pointing out where I went astray:
    Click image for larger version. 

Name:	Books.JPG 
Views:	13 
Size:	42.4 KB 
ID:	24421.Click image for larger version. 

Name:	Junction.JPG 
Views:	13 
Size:	22.4 KB 
ID:	24420Click image for larger version. 

Name:	authors.JPG 
Views:	13 
Size:	42.5 KB 
ID:	24422Click image for larger version. 

Name:	query.JPG 
Views:	12 
Size:	221.9 KB 
ID:	24423Click image for larger version. 

Name:	rltnshp.JPG 
Views:	13 
Size:	37.1 KB 
ID:	24424

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like you have a Cartesian join in the query.
    What is the SQL of Query1?

    Can you post the dB?

  8. #8
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Sorry, I Googled SQL, but still not sure what that means.DatabaseZip.zip. I attached a zipped copy of the db, but the big question mark on the file doesn't bode well for the outcome.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Cartesian join" or "Cartesian product" = Every possible combination of the elements of two or more sets.


    In your query, you didn't add the junction table. You just added the two table, resulting in a "Cartesian join".

    Open your query in design view.
    Add the table "BOOKS-AUTHORS" (don't do anything else)
    Save the query
    Execute (open) the query. You should have 9 records.


    BTW, In table "Books", the field "author_FK" is not needed.


    You have a dash (a special character) in the name of the junction table. This is not recommended.
    Should only use letters and numbers. NO spaces, punctuation or special characters (exception is the underscore)



    If you used proper case (not all uppercase) you could use "CamelBack" formatting (BooksAuthors or Books_Authors). Much easier to read.....

  10. #10
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Yes, I have 9 records now. Thank you, thank you, thank you for resolving that, as well as the tips about the "best practices" I need to address.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Glad to help.

    Good luck with your project.......

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    snowboarder234,

    When you cross post (post same or similar question on multiple sites), you should advise the readers that you have done so.
    Just indicate you have done so, and show the link.
    Cross posted at http://www.utteraccess.com/forum/ind...wtopic=2036413

    Here's why:

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Finding the Authors of a File
    By EddieN1 in forum Programming
    Replies: 1
    Last Post: 02-19-2016, 08:57 AM
  2. Creating an address book
    By faythe1215 in forum Reports
    Replies: 3
    Last Post: 04-09-2015, 06:38 AM
  3. Trouble with Book Database
    By funkymuppet in forum Forms
    Replies: 9
    Last Post: 04-01-2014, 06:30 PM
  4. Price book database
    By tommyried in forum Import/Export Data
    Replies: 9
    Last Post: 12-10-2013, 12:37 AM
  5. Book Exchange Database
    By hikaru12 in forum Database Design
    Replies: 3
    Last Post: 10-28-2013, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums