Results 1 to 7 of 7
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Relationships (2000)

    It's true what they say. . .use it or lose it. I've been out of the loop for too long.

    In a db of club members I want to add a subform to the members form showing a list of books. The list would be the same for all members. I then want to be able to designate which books have been read by each member (check box). I don't get how to "relate" the subform to the main form; I can get the subform to show all the books, but the subform is not unique to each member (the checkbox in the subform doesn't work).

    Help/direction greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    You have a many to many relationship between members and books. Each person can read many books, and each book gets read by many people.

    So you need an intermediate table between people and books, and it is this table that shows in the subform, linked by memberID. The Booktitle would appear in a combo box with a hidden first column.

    The easiest way to do it is not to have a yes/no field in this table, and only have a record when the book has been read. If you want the yes/no field then you need to write code to automatically add a record in this table for each member and each book, with the yes/no field defaulting to no.
    Regards
    John



  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    I actually was headed in the right direction, but the results confused me (see image).

    I need each record to show all of the books so they can see which ones were not read as well. I then need to indicate which were read, and the date they were read. RE: "and it is this table that shows in the subform"; If the subform is based on the intermediate table (tblMembersBooks), how in the world does it display the book title (which is in tblBooks)? Even so, tblMembersBooks is devoid of any records whatsoever. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    As i said in the previous post you need to write code to add a record to the intermediate table for each book and each person. Then whenever you add any new books, you have to go back and add a record for each person.

    To show the book title you either
    * use a combo box with the BookID in column1 (but hidden) and the title in col2, or
    * join the intermediate table to books table in a query and use that to show the title.

    If you are going to have a DateRead field then I don't think need the tick box as well. A book has been read if there is a date, and has not if there is no date.

    As I said befor, it is much easier if the intermediate table only holds books read. That way you create the record when the book is read. To see books not read you could have a second subform below the first (or even just a list box) which is based on a query that finds what has not been read. I won't go into what that query would look like at the moment.
    Regards
    John



  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    John:

    Thanks for your input, but this process isn't going to work for the user (picky, picky, picky). I think I need to go back to the guys and find out what they insist on having, and what they're willing to settle for (i.e. the check box and the date was something they insisted on). Again, I very much appreciate your help.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    You can have both a check box and a date if you want them, but the checkbox is redundant if you also have the date.

    And I bet that after a while you will find records where the date is put in, but the checkbox is not ticked, or vice versa.
    Regards
    John



  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    It's a backwar/forward sort of thing. They have guys who have read a number of the books on the list (months, or even years, ago), so they want to track that they have been read without knowing what date that actually was. Looking forward, they will keep track of the dates. I suggested a random date (01/01/01), but they thought that would be confusing - like redundancy isn't <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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