Results 1 to 2 of 2

Thread: 3 tables - form

  1. #1
    Join Date
    Dec 2005
    Posts
    7

    Unhappy Unanswered: 3 tables - form

    Hi,

    I have 2 tables that have a many-to-many relationship (Book and author). To implement this in access, I have created a junction table (bk_auth).

    Tables:
    Book ---- 1 to many -----> bk_auth
    Author ---- 1 to many ------> bk_auth


    Now, I want to create a form to add new books, with their author. How to do this?

    I thank you all for your support

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    A form/subform approach would be the obvious solution:

    - Create a main form with the record source set to the Book table.

    - Add a subform to the main form with the record source set to the the bk_auth table.

    - When adding the subform, set the master/child relationship between the form/subform to the primary key/foreign key in the Book and bk_auth tables respectively i.e. they are related by ISBN or whatever your PK is.

    The subform wizard will do most of the work for you.

    The downside to this (and I suspect this is really the heartache of your problem) is that you can only add authors to a book providing the authors already exist in the table Author. So using the above method, the user would have to enter all the authors of a book in the Authors table (presumably via another form) then use the above form/subform to enter the book and corresponding authors. Clearly this is cumbersome but does follow database logic.

    So to overcome this, I reckon I would have some kind of input box (unbound) such that you type in the name of the author and a bit of vba will search the author table to see if the author exists. If it does than that author is referenced and a record is created in the bk_auth table & subform, if not, then the vba creates a new author record in the author table then creates a record in the bk_auth table & subform. I suppose if you really wanted to get flash you could get the vba to say check matching surnames so that you can at least do some checking for spelling variations e.g J. Brown instead of John Brown.

    hth
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •